Search code examples
sql-serverdatetimegetdate

SQL Set a dummy date of Today at 5AM

I have a table that shows Date, Area, and Qty. The dates are all null, but I want to set a dummy date/time of today at 5am for all values in the table. I want the date portion to change to today based on whatever today's date is. But, I ant the hour portion to always be 5am.

Date Area Qty
Null A    1
Null B    3
Null C    2

So today, november 5th, the Date field of every record would show 

2018-11-05 05:00:00.000

If the record is still there tomorrow, November 6th, it would change to 

2018-11-06 05:00:00.000

How can I achieve this?

Solution

  • You can use this just to show, or with a job to update the table every day:

    SELECT DATEADD(HH, 5, CONVERT(DATETIME, CONVERT(date, GETDATE())))