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.

Answer 1

You ca try this SQL

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

Answer 2

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)   

Answer 3

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.

Answer 4

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

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

Answer 6

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

-- if data type is different