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.

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:

• DATEADD
• DATEDIFF

Try

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

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.