CODEWITHSUNDEEP
sqlt-sql
eviljack
eviljack

Reputation: 3716

SQL date comparison

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

datediff is driving me nuts...

Upvotes: 1

Views: 1772

Answers (5)

John MacIntyre
John MacIntyre

Reputation: 13031

Try this:

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

Upvotes: 0

StingyJack
StingyJack

Reputation: 19479

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.

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146597

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

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146597

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

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89741

Try:

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

Upvotes: 6

Related Questions