SQL date comparison

Question

How do I check if the timestamp date of a record is before midnight today?

datediff is driving me nuts...

If today was the 2008-11-21, do you mean everything up to and including 2008-11-21 23:59, or 2008-11-21 00:00? Most of the answers so far presume the later. In which case you can just add a day to those results. — knightpfhor, Nov 20 '08 at 21:03
Add 1 Day and subtract 4ms. IIRC SQL handles datetime to the nearest 3rd of a millisecond. So 2008-11-21 23:59:59.997 would get "rounded" into 2008-11-22 00:00:00.000. Its caused a few bugs in some reporting software I was maintaining. — StingyJack, Nov 21 '08 at 12:40

score 6 · Answer 1 · answered Nov 20 '08 at 20:40

6

Try:

WHERE dtColumn < DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

answered Nov 20 '08 at 20:40

Cade Roux

88,164
40
182
265

score 1 · Accepted Answer · edited May 23 '17 at 10:27

1

Here is how to get 0 hour of today in SQL

SELECT (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime))

Just compare your time against that.

Don't use varchar casts since they are slow.

Check this list for more date time help.

edited May 23 '17 at 10:27

Community

1
1

answered Nov 20 '08 at 20:44

StingyJack

19,041
10
63
122

I find the datediff-dateadd method preferable, as shown by @CadeRoux – Mitch Wheat Nov 20 '08 at 23:05
I'd be curious to know if there is a performance difference between them. – StingyJack Nov 21 '08 at 12:37

score 0 · Answer 3 · edited Aug 31 '17 at 18:24

0

Try this:

where myColumn < cast( (cast(getdate() - 0.5 as int)) as datetime)

edited Aug 31 '17 at 18:24

C B

1,677
6
18
20

answered Nov 20 '08 at 20:48

John MacIntyre

12,910
13
67
106

score 0 · Answer 4 · answered Nov 20 '08 at 20:48

Do you mean that the datatype of the column you ate using is "TimeStamp", not dateTime or smalldatetime ?

If so, then you're out of luck. This datatype has nothing to do with dates or times (it's really misleading...) It's just guaranteed to be chronologically unique and sequential... But there's no way ot compare it with a datetime

http://msdn.microsoft.com/en-us/library/aa260631(SQL.80).aspx

http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

score 0 · Answer 5 · answered Nov 20 '08 at 23:00

If ColumnName (column you are examining) is of datatype datetime, and NOT timestamp, then the fastest approach is

Select Case DateDiff(day, columnName, getDate()) 
   When 0 Then 'Today' Else 'Earlier' End
From TableName

assuming all dates values in ColumnName are now or earlier...

Easiest way to think of datediff is that it counts the number of "boundarys" of the specified type that you have to pass over to get from one datetime to another...

SQL date comparison

5 Answers5