sqlsql-servert-sqlsql-server-2014

Remove minutes and seconds and then convert to UTC timezone


I have column with time stamp data like this

2021-12-09 08:01:00.520

I want to remove the minutes and second and then convert the date to UTC timezone in one line.

I tried this code

 SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, Time_Stamp), 0) From Table1

I get this result

2021-12-09 08:00:00.000

and I tried this code

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Time_Stamp)From Table1

I get this result

2021-12-09 16:01:00.520

What I expected is

2021-12-09 16:00:00.000

How to combine the select statement into one line in simple way


Solution

  • Here is another way.

    To remove the minute, seconds and only keep the hour, you may use the following query

    DATEADD(HOUR, DATEDIFF(HOUR, 0, Time_Stamp), 0)
    

    To convert to the UTCDate, just find the different in hour () between GETDATE() and GETUTCDATE() (Since you are not interested in the minute and seconds anyway) and add that to the above query

    Putting all together

    DATEADD(HOUR, DATEDIFF(HOUR, 0, Time_Stamp) + DATEDIFF(HOUR, GETDATE(), GETUTCDATE()), 0)