Correct Way to Write an SQL Query to Select Records

General Discussions
, ,
1

What is the correct way to write an SQL query to select records where a datetime column falls between two dates using sql between dates?

For example, I tried:

select Date, TotalAllowance 
from Calculation 
where EmployeeId = 1
  and Date between 2011/02/25 and 2011/02/27

But it didn’t return the expected results. How should I fix this query when Date is a datetime column?

2

I’ve run into this issue before, and it’s a common one! The query logic you have is on track, but it seems like the date format might be what’s causing the issue. When working with sql between dates, you’ll want to ensure the date literals are wrapped in single quotes and follow the standard YYYY-MM-DD format. So, your query should look like this:

SELECT Date, TotalAllowance  
FROM Calculation  
WHERE EmployeeId = 1  
  AND Date BETWEEN '2011-02-25' AND '2011-02-27'

This should give you the right results, assuming your Date column doesn’t have any time values that could interfere. Just be mindful, the sql between dates will include the start and end dates exactly, so if you have any time portion in your Date column, it may cause issues.

3

Good point, @tim-khorev ! I’ve definitely run into issues when the Date column has a time component (like 2011-02-25 14:30:00). This can cause the query to miss records from the end date, because sql between dates is inclusive, and it might not include records that fall on the exact end date but later in the day. Here’s how I usually work around that:

WHERE Date >= '2011-02-25' AND Date < '2011-02-28'

By changing the sql between dates logic like this, you make sure that the entire day on the 27th is included, and you don’t need to worry about time precision. This is a cleaner approach when dealing with datetime columns where time matters.