T-SQL query with date range

Question

I have a rather strange “error” with a simple query, and I vaguely remember how long I read the reason for this, but would like someone to update my memory.

A table is a basic identification table, a Datetime table.

Inquiry:

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

The problem is that the query results include results in which the Datetime is "2010-04-01 00:00:00". The next day. Which should not.

Is anyone

Greetings

Mu

Answer 1

Take a look at How dates are stored in SQL Server? and How to work with dates in SQL Server?

smalldatetime, 1 , , , datetime 300 miliseconds

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

, ,

< '20100401'

Answer 2

, ; . :

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

Answer 3

:

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

"<" .

, :

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

datetime :

SELECT GETDATE()+1

"23: 59: 59", , :

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'

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

, rowID = 4.

:

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

RowID = 5, .