Studytonight is now part of the GUVI universe. Explore GUVI →
🤩 New Cool Developer Tools for you. Explore →
FREE JavaScript Video Series Start Learning →
  Signup/Sign In
Tests
MCQs to test your knowledge.
Compilers
Compilers to execute code in browser.
← Back to Questions
Ask Question
Not satisfied by the Answer? Still looking for a better solution?
Ask Question

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.
tsql sql-server sql
by

2 Answers

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

keep in mind that the first date is inclusive, but the second is exclusive, as it effectively is '2011/02/27 00:00:00'
pankajshivnani123
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'

Login / Signup to Answer the Question.

LoginSignup