[SOLVED] Remove minutes and seconds and then convert to UTC timezone

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)