1

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.

c00000fd
  • 20,994
  • 29
  • 177
  • 400
  • 1
    You are not comparing for equality. You are comparing "<=". I wouldn't worry for a less-than-or-equals to. An equals to is another matter, since two values that might have the same YYYY-MM-DD HH-MI-SS representation might not be equal. – Gordon Linoff Sep 10 '12 at 00:22
  • My concern is picking dates as '2012-09-09 22:30:00' in the example above. – c00000fd Sep 10 '12 at 00:31
  • Why not using a BETWEEN then? – Niladri Biswas Sep 10 '12 at 02:42
  • Datetime. Note TIME. You really think it only compares date. This is easy to test. If you pass milliseconds since midnight of 1980 you are on your own. -1 – paparazzo Sep 10 '12 at 03:19
  • @GordonLinoff: If two values, say *x* and *y* are not equal, how does comparing them with `<=` solve the problem? Consider an *x* and a *y* that would be equal if computed exactly, but some rounding issue has made them slightly different. Ideally, we want to accept them as equal (and therefore to accept them as less than or equal to). If they are not equal in the direction that *x* < *y*, then `x <= y` accepts them as less than or equal to, as desired, but, if they are not equal in the direction that *x* > *y*, then `x <= y` does not accept them as less than or equal to. – Eric Postpischil Sep 11 '12 at 14:06

1 Answers1

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

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • @ypercube - `select MONTH('20120909T22:30:00')` gives an error - it can't even convert it. For a date *without* time, the undashed variant is the best bet. – Damien_The_Unbeliever Sep 10 '12 at 06:23