SQL query to select dates between two dates

问题:

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.

回答1:

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 >= \'2011/02/25\' and Date <= \'2011/02/27\'

回答2:

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 <.

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 >= \'2011/02/25\' and Date < \'2011/02/28\'

OR

select Date,TotalAllowance from Calculation where EmployeeId=1 
and Date >= \'2011/02/25\' and Date <= \'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\'

回答3:

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.

回答4:

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

回答5:

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.

回答6:

select * from test 
     where CAST(AddTime as datetime) between \'2013/4/4\' and \'2014/4/4\'

-- if data type is different

回答7:

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 >= \'11 Dec 2016\';

Note also that Access will accept the #, thus:

SELECT FirstName FROM Students WHERE DoB >= #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 >= \" & 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 < 13, just different results!

As for the question asked, add one day to the last date and make the comparison as follows:

dated >= \'11 Nov 2016\' AND dated < \'15 Nov 2016\' 

回答8:

select Date,TotalAllowance 
from Calculation 
where EmployeeId=1
  and convert(varchar(10),Date,111) between \'2011/02/25\' and \'2011/02/27\'

回答9:

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)   

回答10:

Try putting the dates between # # for example:

#2013/4/4# and #2013/4/20#

It worked for me.

--- EDIT --- I received a notification that I lost two reputation points because somebody down-voted this answer. Please, don\'t just down-vote if the answer doesn\'t work for You. Ask for further info/help in the comments, or check other solutions.

I don\'t care about reputation points - I just say that down-votes are not made for that.

回答11:

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\'

回答12:

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) >=  Convert(Varchar(10),\'01-01-2015\',111) and  Convert(Varchar(10),CreatedDate,111) <= 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**

回答13:

Select 
    * 
from 
    Calculation 
where 
    EmployeeId=1 and Date between #2011/02/25# and #2011/02/27#;

回答14:

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,\'\" & dt & \"\',[date])>=0 and datediff(day,\'\" & dt2 & \"\',[date])<=0 \"

here calender is the Table, dt as the starting date variable and dt2 is the finishing date variable.

回答15:

I like to use the syntax \'1 MonthName 2015\' for dates ex:

   WHERE aa.AuditDate>=\'1 September 2015\'
     AND aa.AuditDate<=\'30 September 2015\'

for dates

回答16:

I would go for

select Date,TotalAllowance from Calculation where EmployeeId=1
             and Date >= \'2011/02/25\' and Date < DATEADD(d, 1, \'2011/02/27\')

The logic being that >= includes the whole start date and < 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 >= $start_month_day_1 and Date < DATEADD(m, 1, $end_month_day_1)

回答17:

You ca try this SQL

select * from employee where rec_date between \'2017-09-01\' and \'2017-09-11\' 

回答18:

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

回答19:

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\');