[sql] SQL "between" not inclusive

I have a query like this:

SELECT * FROM Cases WHERE created_at BETWEEN '2013-05-01' AND '2013-05-01'

But this gives no results even though there is data on the 1st.

created_at looks like 2013-05-01 22:25:19, I suspect it has to do with the time? How could this be resolved?

It works just fine if I do larger date ranges, but it should (inclusive) work with a single date too.

This question is related to sql sql-server sql-server-2008 tsql date

The answer is


I find that the best solution to comparing a datetime field to a date field is the following:

DECLARE @StartDate DATE = '5/1/2013', 
        @EndDate   DATE = '5/1/2013' 

SELECT * 
FROM   cases 
WHERE  Datediff(day, created_at, @StartDate) <= 0 
       AND Datediff(day, created_at, @EndDate) >= 0 

This is equivalent to an inclusive between statement as it includes both the start and end date as well as those that fall between.


Dyamic date BETWEEN sql query

var startDate = '2019-08-22';
var Enddate = '2019-10-22'
     let sql = "SELECT * FROM Cases WHERE created_at BETWEEN '?' AND '?'";
     const users = await mysql.query( sql, [startDate, Enddate]);

Just use the time stamp as date:

SELECT * FROM Cases WHERE date(created_at)='2013-05-01' 

You can use the date() function which will extract the date from a datetime and give you the result as inclusive date:

SELECT * FROM Cases WHERE date(created_at)='2013-05-01' AND '2013-05-01'

cast(created_at as date)

That will work only in 2008 and newer versions of SQL Server

If you are using older version then use

convert(varchar, created_at, 101)

your code

SELECT * FROM Cases WHERE created_at BETWEEN '2013-05-01' AND '2013-05-01'

how SQL reading it

SELECT * FROM Cases WHERE '2013-05-01 22:25:19' BETWEEN '2013-05-01 00:00:00' AND '2013-05-01 00:00:00'

if you don't mention time while comparing DateTime and Date by default hours:minutes:seconds will be zero in your case dates are the same but if you compare time created_at is 22 hours ahead from your end date range

if the above is clear you fix this in many ways like putting ending hours in your end date eg BETWEEN '2013-05-01' AND ''2013-05-01 23:59:59''

OR

simply cast create_at as date like cast(created_at as date) after casting as date '2013-05-01 22:25:19' will be equal to '2013-05-01 00:00:00'


You need to do one of these two options:

  1. Include the time component in your between condition: ... where created_at between '2013-05-01 00:00:00' and '2013-05-01 23:59:59' (not recommended... see the last paragraph)
  2. Use inequalities instead of between. Notice that then you'll have to add one day to the second value: ... where (created_at >= '2013-05-01' and created_at < '2013-05-02')

My personal preference is the second option. Also, Aaron Bertrand has a very clear explanation on why it should be used.


It has been assumed that the second date reference in the BETWEEN syntax is magically considered to be the "end of the day" but this is untrue.

i.e. this was expected:

SELECT * FROM Cases 
WHERE created_at BETWEEN the beginning of '2013-05-01' AND the end of '2013-05-01'

but what really happen is this:

SELECT * FROM Cases 
WHERE created_at BETWEEN '2013-05-01 00:00:00+00000' AND '2013-05-01 00:00:00+00000'

Which becomes the equivalent of:

SELECT * FROM Cases WHERE created_at = '2013-05-01 00:00:00+00000'

The problem is one of perceptions/expectations about BETWEEN which does include BOTH the lower value and the upper values in the range, but does not magically make a date the "beginning of" or "the end of".

BETWEEN should be avoided when filtering by date ranges.

Always use the >= AND < instead

SELECT * FROM Cases 
WHERE (created_at >= '20130501' AND created_at < '20130502')

the parentheses are optional here but can be important in more complex queries.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to date

How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Typescript Date Type? how to convert current date to YYYY-MM-DD format with angular 2 SQL Server date format yyyymmdd Date to milliseconds and back to date in Swift Check if date is a valid one change the date format in laravel view page Moment js get first and last day of current month How can I convert a date into an integer?