Remove minutes and seconds and then convert to UTC timezone-CodePudding

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

CodePudding user response：

Convert the date to a string and truncate to remove the minutes and seconds. Then add the difference in minutes between your timezone and UTC.

select dateadd(minute,datediff(minute,getdate(),getutcdate()),convert(datetime2(0),convert(varchar(13),getdate(),126) ':00:00',126));

CodePudding user response：

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), dateadd(hour, datediff(hour, 0, Time_Stamp), 0)) FROM Table1

CodePudding user response：

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)