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

How to forward messages (eg. mouse wheel) to another Control without stealing focus and without P/Invoke?

c# focus mousewheel message-pump imessagefilter

I want to forward a message (such as WM_MOUSEWHEEL) when I'm over this control with the mouse, without stealing the focus. This problem can be easily solved intercepting the message with an

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.