sql server time compare with offset

Question

I've got a select query that returns a list of rows that have expired

  SELECT *
  FROM dbo.myTable
  WHERE  endDate < Convert(datetime, Convert(int, GetDate()))

how can I get it to use current day at 2AM instead of the current time I get from GetDate()

You mean the timestamp for this morning at 2AM? Have you tried date_sub() or date_add() — Reza S, May 07 '12 at 23:10
yes, though I suppose I could pass it using the server-side language. — Daniel, May 07 '12 at 23:12
Can't you just get the current date at midnight and add 2Hours to it? — Reza S, May 07 '12 at 23:13

score 1 · Accepted Answer · answered May 07 '12 at 23:15

1

To get the current day at 2AM:

convert(datetime,left(convert(varchar, getdate(), 121),10) + ' 02:00')

answered May 07 '12 at 23:15

hkf

1

It's a format code. Have a look at http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx – hkf May 08 '12 at 04:51
1

@Daniel: Note that the [121 format](http://msdn.microsoft.com/en-us/library/ms187928.aspx "CAST and CONVERT (Transact-SQL)") is locale-dependent, even though it is the so called ODBC canonical format. It may well work with your locale settings, of course. Still, if you want it to be locale-independent, use the 112 format instead (and, accordingly, cut off 8 left characters instead of 10). Also, please pay attention to [this question](http://stackoverflow.com/q/1177449/297408 "Best approach to remove time part of datetime in SQL Server"). – Andriy M May 09 '12 at 16:46

1 Answers1