Is it safe comparing tSQL's dates for equality?

Say, if 'dtIn' is of type DATETIME2, is it safe to do the following comparison using SQL Server 2008?

--my concern is about equality part, 
-- or will it pick ALL 2012-09-09 22:30:00 dates?
SELECT * FROM tbl WHERE [dtIn] <= '2012-09-09 22:30:00'

The reason I'm asking is a possible situation when such comparison can be "bad" in a programming language where dates are stored as 'double's, or number of milliseconds since midnight of 1980, or something like that. Such value is stored as a floating point number, that is always a bad idea to compare for equality.

Answers


SELECT * FROM tbl WHERE [dtIn] <= '2012-09-09T22:30:00'

This will only select rows where dtIn is exactly equal to '2012-09-09T22:30:00' or earlier. It will not select rows where dtIn is equal to, say '2012-09-09T22:30:00.003'.

I'd usually recommend selecting an exclusive end point for date/time comparisons - they're a lot easier to reason about:

SELECT * FROM tbl WHERE [dtIn] < '2012-09-09T22:30:01'

(Note - I've inserted T between the date and time in my literals. For conversions to datetime2, I think the spaced version is safe, but if you're dealing with datetime columns, the spaced version can be ambigous - so I tend to play things safe)


Need Your Help

I have a problem in AJAX

php javascript ajax

I have a problem with some of my validation code. Here it is.