T-SQL query with date range

sql-server, sql-server-2005, t-sql

Solution

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'

Problem

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

Original source