CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Simon Lang
Simon Lang

Reputation: 42655

sql server datetime where clause

Sorry for the lazy question. I want to know the SQL Server 2005 syntax for

SELECT * 
  FROM myTable 
 WHERE myDateTimeField > "4am this morning"

Thanks in advance for your help.

Upvotes: 2

Views: 1890

Answers (3)

mattmc3
mattmc3

Reputation: 18325

And just to be different, and arguably shorter/more readable than the other offerings:

SELECT * 
  FROM myTable 
 WHERE myDateTimeField > 
       cast(convert(varchar, getdate(), 101) + ' 4AM' as datetime)

Of course, you might not really want to hard-code 4AM, but if you do, this way is nice and obvious in conveying your intent.

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146499

Try

SELECT * FROM myTable
WHERE myDateTimeField >
      DateAdd(hour, 4, DateAdd(day, DateDiff(day, 0, getdate()), 0))

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332571

Use:

SELECT * 
  FROM myTable 
 WHERE myDateTimeField > DATEADD(hh, 4, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0))

This:

DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

...will return a DATETIME at midnight of the current day. So the outer DATEADD adds four hours to that date at midnight to give you a DATETIME at 4 AM this morning.

Reference:

Upvotes: 3

Related Questions