SQL query to select dates between two dates

Question

I have a start date and end date  I want to get the list of dates in between these two dates  Can anyone help me pointing the mistake in my query   select Date TotalAllowance  from Calculation  where EmployeeId 1   and Date between 2011 02 25 and 2011 02 27   Here Date is a datetime variable

User · Answer

There are a lot of bad answers and habits in this thread, when it comes to selecting based on a date range where the records might have non-zero time values - including the second highest answer at time of writing.

Never use code like this: Date between '2011/02/25' and '2011/02/27 23:59:59.999'

Or this: Date >= '2011/02/25' and Date <= '2011/02/27 23:59:59.999'

To see why, try it yourself:

DECLARE @DatetimeValues TABLE
    (MyDatetime datetime);
INSERT INTO @DatetimeValues VALUES
    ('2011-02-27T23:59:59.997')
    ,('2011-02-28T00:00:00');

SELECT MyDatetime
FROM @DatetimeValues
WHERE MyDatetime BETWEEN '2020-01-01T00:00:00' AND '2020-01-01T23:59:59.999';

SELECT MyDatetime
FROM @DatetimeValues
WHERE MyDatetime >= '2011-02-25T00:00:00' AND MyDatetime <= '2011-02-27T23:59:59.999';

In both cases, you'll get both rows back. Assuming the date values you're looking at are in the old datetime type, a date literal with a millisecond value of 999 used in a comparison with those dates will be rounded to millisecond 000 of the next second, as datetime isn't precise to the nearest millisecond. You can have 997 or 000, but nothing in between.

You could use the millisecond value of 997, and that would work - assuming you only ever need to work with datetime values, and not datetime2 values, as these can be far more precise. In that scenario, you would then miss records with a time value 23:59:59.99872, for example. The code originally suggested would also miss records with a time value of 23:59:59.9995, for example.

Far better is the other solution offered in the same answer - Date >= '2011/02/25' and Date < '2011/02/28'. Here, it doesn't matter whether you're looking at datetime or datetime2 columns, this will work regardless.

The other key point I'd like to raise is date and time literals. '2011/02/25' is not a good idea - depending on the settings of the system you're working in this could throw an error, as there's no 25th month. Use a literal format that works for all locality and language settings, e.g. '2011-02-25T00:00:00'.

User · Answer

Since a datetime without a specified time segment will have a value of date 00 00 00 000  if you want to be sure you get all the dates in your range  you must either supply the time for your ending date or increase your ending date and use  lt    select Date TotalAllowance from Calculation where EmployeeId 1  and Date between  2011 02 25  and  2011 02 27 23 59 59 999    OR  select Date TotalAllowance from Calculation where EmployeeId 1  and Date  gt    2011 02 25  and Date  lt   2011 02 28    OR   select Date TotalAllowance from Calculation where EmployeeId 1  and Date  gt    2011 02 25  and Date  lt    2011 02 27 23 59 59 999    DO NOT use the following  as it could return some records from 2011 02 28 if their times are 00 00 00 000   select Date TotalAllowance from Calculation where EmployeeId 1  and Date between  2011 02 25  and  2011 02 28

User · Answer

I would go for  select Date TotalAllowance from Calculation where EmployeeId 1              and Date  gt    2011 02 25  and Date  lt  DATEADD d  1   2011 02 27     The logic being that  gt   includes the whole start date and  lt  excludes the end date  so we add one unit to the end date  This can adapted for months  for instance   select Date      from                  where Date  gt    start month day 1 and Date  lt  DATEADD m  1   end month day 1

User · Answer

SELECT Date  TotalAllowance   FROM Calculation   WHERE EmployeeId   1    AND Date BETWEEN to date  2011 02 25   yyyy-mm-dd                   AND to date   2011 02 27   yyyy-mm-dd

User · Answer

select Date TotalAllowance  from Calculation  where EmployeeId 1   and convert varchar 10  Date 111  between  2011 02 25  and  2011 02 27

User · Answer

we can use between to show two dates data but this will search the whole data and compare so it will make our process slow for huge data  so i suggest everyone to use datediff   qry    SELECT   FROM  calender  WHERE datediff day     amp  dt  amp      date   gt  0 and datediff day     amp  dt2  amp      date   lt  0     here calender is the Table  dt as the starting date variable and dt2 is the finishing date variable

User · Answer

This is very old  but given a lot of experiences I have had with dates  you might want to consider this  People use different regional settings  as such  some people  and some databases computers  depending on regional settings  may read this date 11 12 2016 as 11th Dec 2016 or Nov 12  2016  Even more  16 11 12 supplied to MySQL database will be internally converted to 12 Nov 2016  while Access database running on a UK regional setting computer will interpret and store it as 16th Nov 2012   Therefore  I made it my policy to be explicit whenever I am going to interact with dates and databases  So I always supply my queries and programming codes as follows   SELECT FirstName FROM Students WHERE DoB  gt    11 Dec 2016     Note also that Access will accept the    thus   SELECT FirstName FROM Students WHERE DoB  gt    11 Dec 2016     but MS SQL server will not  so I always use       as above  which both databases accept   And when getting that date from a variable in code  I always convert the result to string as follows    SELECT FirstName FROM Students WHERE DoB  gt      amp  myDate ToString  d MMM yyyy     I am writing this because I know sometimes some programmers may not be keen enough to detect the inherent conversion  There will be no error for dates  lt  13  just different results   As for the question asked  add one day to the last date and make the comparison as follows   dated  gt    11 Nov 2016  AND dated  lt   15 Nov 2016

User · Answer

if its date in 24 hours and start in morning and end in the night should add something like     declare  Approval date datetime set  Approval date  getdate   Approval date between  Approval date    00 00 00 000  and  Approval date    23 59 59 999

User · Answer

Really all sql dates should be in yyyy-MM-dd format for the most accurate results

User · Answer

Check below Examples  Both working and Non-Working   select   from tblUser Where     convert varchar 10  CreatedDate 111  between  2015 04 01  and  2016 04 01    --  Working     OR  select   from tblUser Where  CAST CreatedDate AS DATETIME  between CAST  2015 04 01  AS DATETIME  And CAST  2016 4 30 AS DATETIME     --  Working     OR  select   from tblUser Where  YEAR CreatedDate  between YEAR  2015 04 01   And YEAR  2016 4 30       --  Working     AND below is not working   select   from tblUser Where Convert Varchar 10  CreatedDate 111   gt    Convert Varchar 10   01-01-2015  111  and  Convert Varchar 10  CreatedDate 111   lt   Convert Varchar 10   31-12-2015  111    --  Not Working     select   from tblUser Where  Convert Varchar 10  CreatedDate 111  between Convert Varchar 10   01-01-2015  111  And Convert Varchar 10   31-12-2015  111     --  Not Working

User · Answer

best query for the select date between current date and back three days     select Date TotalAllowance from Calculation where EmployeeId 1 and Date BETWEEN        DATE SUB CURDATE    INTERVAL 3 DAY   AND CURDATE      best query for the select date between current date and next three days     select Date TotalAllowance from Calculation where EmployeeId 1 and Date BETWEEN       CURDATE    AND DATE ADD CURDATE    INTERVAL 3 DAY

User · Answer

select   from test       where CAST AddTime as datetime  between  2013 4 4  and  2014 4 4    -- if data type is different

User · Answer

Script for SelectTopNRows command from SSMS          SELECT TOP 10  Id      Id parvandeh      FirstName      LastName      RegDate      Gilder      Nationality      Educ      PhoneNumber      DueInMashhad       EzdevajDate        MarriageStatus      Gender      Photo       ModifiedOn      CreatorIp     From    dbo   Socials  where educ  gt   3 or EzdevajDate   gt    1992 03 31  and EzdevajDate  lt    2019 03 09  and MarriageStatus   1

User · Answer

Select         from      Calculation  where      EmployeeId 1 and Date between  2011 02 25  and  2011 02 27

User · Answer

you should put those two dates between single quotes like    select Date  TotalAllowance from Calculation where EmployeeId   1              and Date between  2011 02 25  and  2011 02 27    or can use  select Date  TotalAllowance from Calculation where EmployeeId   1              and Date  gt    2011 02 25  and Date  lt    2011 02 27    keep in mind that the first date is inclusive  but the second is exclusive  as it effectively is  2011 02 27 00 00 00

User · Answer

I like to use the syntax  1 MonthName 2015  for dates ex      WHERE aa AuditDate gt   1 September 2015       AND aa AuditDate lt   30 September 2015    for dates

User · Answer

it s better write this way   CREATE PROCEDURE dbo Get Data By Dates        EmployeeId INT   1       Start Date DATE       End Date Date   AS Select   FROM Calculation       where EmployeeId  EmployeeId AND Test Date BETWEEN  Start Date AND  End Date RETURN

User · Answer

select   from table name where col Date between  2011 02 25   AND DATEADD s -1 DATEADD d 1  2011 02 27      Here  first add a day to the current endDate  it will be 2011-02-28 00 00 00  then you subtract one second to make the end date 2011-02-27 23 59 59  By doing this  you can get all the dates between the given intervals   output  2011 02 25 2011 02 26 2011 02 27

User · Answer

Try putting the dates between     for example    2013 4 4  and  2013 4 20    It worked for me

User · Answer

You ca try this SQL  select   from employee where rec date between  2017-09-01  and  2017-09-11

User · Answer

This query stands good for fetching the values between current date and its next 3 dates  SELECT   FROM tableName  WHERE columName  BETWEEN CURDATE   AND DATE ADD CURDATE    INTERVAL 3 DAY    This will eventually add extra 3 days of buffer to the current date

User · Answer

Try this   select Date TotalAllowance from Calculation where EmployeeId 1              and  Date  between  2011 02 25  and  2011 02 27    The date values need to be typed as strings   To ensure future-proofing your query for SQL Server 2008 and higher  Date should be escaped because it s a reserved word in later versions   Bear in mind that the dates without times take midnight as their defaults  so you may not have the correct value there

[sql] SQL query to select dates between two dates

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to datetime

Examples related to sql-server-2005