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.
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)