How to convert local time to UTC time in SQL

For your First question,

if you change hour to minute or second or any date part, SQL sever puts your Local Time-UTC Difference in that unit. It means, suppose difference is 5 hours between your local time and UTC time and you use 'Seconds'segment here instead of 'Hour', SQL server converts that time difference into seconds (i.e 18000 seconds). Even if you use 'DATE' part, SQL server will give the same UTC time.

For your second question, it takes care your date, month year change it's because you are using Built-in SQL method 'DATEADD()' that is supposed to handle all of these date change issues.

DECLARE @DIFF INT

SET @DIFF = DATEDIFF(HOUR,GETDATE(),GETUTCDATE())

SELECT @DIFF AS DIFFERENCE_IN_HOUR

SELECT DATEADD(HOUR, @DIFF, GETDATE()) AS CURRENT_UTC_TIME

------------------------------------------------------------

DECLARE @DIFF1 INT

SET @DIFF1 = DATEDIFF(MINUTE,GETDATE(),GETUTCDATE())

SELECT @DIFF1 AS DIFFERENCE_IN_MINUTE

SELECT DATEADD(MINUTE, @DIFF1, GETDATE()) AS CURRENT_UTC_TIME

-------------------------------------------------------------

DECLARE @DIFF2 INT

SET @DIFF2 = DATEDIFF(SECOND,GETDATE(),GETUTCDATE())

SELECT @DIFF2 AS DIFFERENCE_IN_SECONDS

SELECT DATEADD(SECOND, @DIFF2, GETDATE()) AS CURRENT_UTC_TIME

------------------------------------------------------------

DECLARE @DIFF3 INT

SET @DIFF3 = DATEDIFF(DAY,GETDATE(),GETUTCDATE())

SELECT @DIFF3 AS DIFFERENCE_IN_DAYS

SELECT DATEADD(DAY, @DIFF3, GETDATE()) AS CURRENT_UTC_TIME

-----------------------------------------------------------

you may look the sql query result: