T-SQL query with date range

Question

Hi,

I have a fairly weird 'bug' with a simple query, and I vaguely remember reading the reason for it somewhere a long time ago but would love someone to refresh my memory.

The table is a basic ID, Datetime table.

The query is:

select ID, Datetime from Table where Datetime <= '2010-03-31 23:59:59'

The problem is that the query results include results where the Datetime is '2010-04-01 00:00:00'. The next day. Which it shouldn't.

Anyone?

Cheers

Moo

Answer 1

It's very odd that you are seeing that; I don't know why. But I will suggest that you write the query this way instead:

select ID, Datetime from Table where Datetime < '2010-04-01'

Answer 2

try doing it like:

select ID, Datetime from Table where Datetime < '2010-04-01'

I always floor the datetime and increment the day and just use "<" less than.

to floor a datetime to just the day use:

SELECT DATEADD(day,DATEDIFF(day,0,  GETDATE()   ),0) 

you can easily increment a datetime by using addition:

SELECT GETDATE()+1

by using the '23:59:59' you can miss rows, try it out:

DECLARE @YourTable table (RowID int, DateOf datetime)
INSERT INTO @YourTable VALUES (1,'2010-03-31 10:00')
INSERT INTO @YourTable VALUES (2,'2010-03-31')
INSERT INTO @YourTable VALUES (3,'2010-03-31 23:59:59')
INSERT INTO @YourTable VALUES (4,'2010-03-31 23:59:59.887')
INSERT INTO @YourTable VALUES (5,'2010-04-01')
INSERT INTO @YourTable VALUES (6,'2010-04-01 10:00')
select * from @YourTable where DateOf <= '2010-03-31 23:59:59'

OUTPUT

RowID       DateOf
----------- -----------------------
1           2010-03-31 10:00:00.000
2           2010-03-31 00:00:00.000
3           2010-03-31 23:59:59.000

(3 row(s) affected

this query is wrong, because it does not find the missed rowID=4 record.

if you try to fix this with:

select * from @YourTable where DateOf <= '2010-03-31 23:59:59.999'

then RowID=5 will be included as well, which is wrong.

Answer 3

Take a look at How Are Dates Stored In SQL Server? and How Does Between Work With Dates In SQL Server?

If that is a smalldatetime it has 1 minute precision so if rounds up, for datetime it is 300 miliseconds

example

DECLARE @d DATETIME
SELECT @d = '2001-12-31 23:59:59.999'

SELECT @d

2002-01-01 00:00:00.000

DECLARE @d DATETIME
SELECT @d = '2001-12-31 23:59:59.998'

SELECT @d

2001-12-31 23:59:59.997

Always use less than next day at midnight, in your case

< '20100401'