Sql "between" not inclusive

Overview: The details matter in dates

Factoring Time with Dates

In SQL, the data type datetime treats 'YYYY-MM-DD' as 'YYYY-MM-DD 00:00:00'. If your need is to include an entire day's data, like for '2021-01-31', you should form your SQL query so that the end date is less than the sequential day:

-- Knock, knock! Who's there? Next day!
SELECT * FROM your_table WHERE created_at >= '2021-01-01' AND created_at < DATEADD(day, 1, '2021-01-31');


Date-only Filtering in Datetime Column

When dealing with a single date search in a datetime column, the CAST function can bring you closer to accuracy:

-- Bye-bye time problem, Hello single date beauty!
SELECT * FROM your_table WHERE CAST(created_at AS date) BETWEEN '2021-01-01' AND '2021-01-01';


Practice Variable Declarations

When using functions like DATEDIFF, it's a good practice to declare your variables beforehand:

-- Time to declare! independence..no wait..dates!!
DECLARE @StartDate DATE = '2021-01-01', @EndDate DATE = '2021-01-31';
SELECT * FROM your_table WHERE created_at >= @StartDate AND created_at < DATEADD(day, 1, @EndDate);


Parentheses for grouping

Use parentheses for better clarity in complex queries, especially those involving multiple inequalities:

-- Grouping clutters is cleaning!
SELECT * FROM your_table WHERE (created_at >= '2021-01-01') AND (created_at < DATEADD(day, 1, '2021-01-31'));


Digging Deeper: More nuances of date comparison

Using DATE function

To fetch data at date-level precision, leverage the DATE function:

-- Talk about a DATE, huh!
SELECT * FROM your_table WHERE DATE(created_at) = '2021-01-01';


Matching Date Formats

Be sure that the date format in your query matches your database's to avoid perennial facepalm 🤦‍♂️ situations:

-- Talk to me in my language, YYYY-MM-DD it is!
SELECT * FROM your_table WHERE created_at >= '2021-01-01'


Inclusive Query for Time Interval

Managing inclusivity within specific time intervals eliminates any off-by-a-minute errors:

-- Wanna meet? Let's set an absolute time!
SELECT * FROM your_table WHERE created_at >= '2021-01-01 00:00:00' AND created_at < '2021-02-01 00:00:00';
