Why does the equals operator fail in SQL when retrieving records for the same date, but greater than and less than work?

Data and Databases
1

I’m trying to retrieve customer records created on the date 2021-10-14 (formatted as yyyy-mm-dd). Here’s my query:

SELECT * FROM customers WHERE created_at = '2021-10-14';

This returns zero results. However, this query correctly fetches data for that day:

SELECT * FROM customers WHERE created_at > '2021-10-14' AND created_at < '2021-10-15';

Can someone explain why the equality check isn’t functioning as expected while the range check does include records for that date?

2

One reason could be that the ‘created_at’ column stores datetime values, not just date. The “>” and “<” operators consider time too, so they work. But ‘=’ compares exact strings. Try using “created_at >= ‘2021-10-14 00:00:00’ AND created_at < ‘2021-10-15 00:00:00’” to include specific time.

3

The situation you’re experiencing is quite common in SQL queries when dealing with datetime fields. Often, databases store timestamps along with date values, so when you use the “=” operator, it expects an exact match, including time. Since your data likely includes time as well, the equality fails unless there’s an exact match at midnight. On the other hand, using range operators allows you to encompass any time within that day. Adjusting your comparison to disregard the time portion or using a method to cast or extract just the date part can help in such scenarios.

4

hmm, could timezone differences be at play here? sometimes, databases store timestamps in UTC, but the app may display them in local time. have you tried extracting the date part only from ‘created_at’, like using a function to cast or format dates? that might shed some light too.