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.
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';
SQL Server DATEDIFF() Function The DATEDIFF() function returns the difference between two dates.
SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME BETWEEN '2001-03-01 11:00:00' AND '2005-03-01 22:00:00';
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With