Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server : fetching records between two dates?

In SQL I write a SELECT statement to fetch data between two dates, using between and

Ex:

select * 
from xxx 
where dates between '2012-10-26' and '2012-10-27'

But the rows returned are for 26th only, not 26th and 27th.

Can you help me? Thank you.

like image 341
Ssasidhar Avatar asked Oct 27 '12 10:10

Ssasidhar


People also ask

How do I fetch records between two dates in SQL Server?

As stated above, the format of date and time in our table shall be yyyy:mm: dd hh:mm: ss which is implied by DATETIME2. The time is in a 24-hour format. Syntax: SELECT * FROM TABLE_NAME WHERE DATE_TIME_COLUMN BETWEEN 'STARTING_DATE_TIME' AND 'ENDING_DATE_TIME';

How do I select days between two dates in SQL?

SQL Server DATEDIFF() Function The DATEDIFF() function returns the difference between two dates.

How do I select a specific date range in SQL?

SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME BETWEEN '2001-03-01 11:00:00' AND '2005-03-01 22:00:00';


1 Answers

As others have answered, you probably have a DATETIME (or other variation) column and not a DATE datatype.

Here's a condition that works for all, including DATE:

SELECT * 
FROM xxx 
WHERE dates >= '20121026' 
  AND dates <  '20121028'    --- one day after 
                             --- it is converted to '2012-10-28 00:00:00.000'
 ;

@Aaron Bertrand has blogged about this at: What do BETWEEN and the devil have in common?

like image 121
ypercubeᵀᴹ Avatar answered Nov 08 '22 08:11

ypercubeᵀᴹ