SQL Set a dummy date of Today at 5AM

Question

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?

Are you OK with a `SELECT` statement that retrieves the fake date for that column? Or do you want to actually store that value in the table? If storing in the table maybe a job that runs nightly and updates the date column. Otherwise you can probably do something like `select '2018-11-05 05:00:00.000' as date, area, qty from MyTable;` — Nicholas Hirras, Nov 05 '18 at 19:59

Ilyes · Answer 1 · 2018-11-05T20:15:16.473

1

Simply

UPDATE YourTable
SET [Date] = (SELECT 
                   CAST(CAST(GetDate() AS DATE) AS DATETIME) + 
                   CAST('05:00:00' AS DATETIME)
             );

If you need to do this every day, you can schedule a job to run this query every day.

edited Nov 05 '18 at 20:15

answered Nov 05 '18 at 20:05

Ilyes

14,640
4
29
55

score 0 · Accepted Answer · answered Nov 05 '18 at 20:05

0

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())))

answered Nov 05 '18 at 20:05

fnightangel

416
3
10

score 0 · Answer 3 · edited Nov 05 '18 at 20:15

0

Just add + 1 to the existing date and it will increment by 1 day

UPDATE YourTable
SET [Date] = [Date] + 1

edited Nov 05 '18 at 20:15

Ilyes

14,640
4
29
55

answered Nov 05 '18 at 20:13

China Syndrome

953
12
24

score 0 · Answer 4 · answered Nov 05 '18 at 20:19

You can convert that column to a calculated column (that is what you are describing after all):

alter table myTable drop column [Date];
alter table myTable add [Date] as 
  cast(cast(getdate() as date) as datetime) + 
  cast('05:00 AM' as datetime);

DBFiddle demo

SQL Set a dummy date of Today at 5AM

4 Answers4