question

mmoha51443 avatar image
mmoha51443 asked

Can you tell me how to convert UTC time into local time? below is my query...

Select A.[SCH_ALARM_SK], A.[NOM_DATE] Date_Integer, DATEADD(day, -2, A.[NOM_DATE]) Date, C.[ID], C.[SORT_NAME], B.DESCR,
DATEADD(mi, A.[START_FNMOMENT]/60, '12/30/1899 00:00:00') AS START,
DATEADD(mi, A.[RAISE_FNMOMENT]/60, '12/30/1899 00:00:00') AS STOP,
DATEADD(mi, A.[CLEAR_FNMOMENT]/60, '12/30/1899 00:00:00') AS CLEAR
from [dbo].[SCH_ALARM_INST] A
Inner Join [dbo].[SCH_ALARM] B
ON A.[SCH_ALARM_SK] = B.[SCH_ALARM_SK]
Inner Join [dbo].[EMP] C
ON C.[EMP_SK] = A.[EMP_SK]
where DATEADD(day, -2, [NOM_DATE]) between '2019-03-01 00:00:00.000' and '2019-05-31 00:00:00.000'
and A.[SCH_ALARM_SK] IN ('-988632205052')

convert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

You can use SWITCHOFFSET to change time zones if you know the offset for the local time zone. If not, you can calculate that. The server I'm using is set to UTC, so in this example I'm first generating a date in US Eastern Standard Time to then demonstrate how to convert to UTC.

DECLARE @datetime DATETIMEOFFSET = CONVERT(datetimeoffset, SYSUTCDATETIME()) AT TIME ZONE 'US Eastern Standard Time';
DECLARE @offset char(6) = RIGHT(@datetime, 6);
SELECT @datetime, @offset, SWITCHOFFSET(SYSUTCDATETIME(), @offset);

The offset is a string in the format "-00:00". It appears at the end of any datetimeoffset string value. I hope this helps.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mmoha51443 avatar image
mmoha51443 answered

sorry I am new to sql … I am getting lot of errors, can you tell me what I am doing wrong?

Select A.[SCH_ALARM_SK], A.[NOM_DATE] Date_Integer, DATEADD(day, -2, A.[NOM_DATE]) Date, C.[ID], C.[SORT_NAME], B.DESCR,
DATEADD(mi, A.[START_FNMOMENT]/60, '12/30/1899 00:00:00') AS START,
DATEADD(mi, A.[RAISE_FNMOMENT]/60, '12/30/1899 00:00:00') AS STOP,
DATEADD(mi, A.[CLEAR_FNMOMENT]/60, '12/30/1899 00:00:00') AS CLEAR,
COALESCE(DATEDIFF(SS, DATEADD(mi, A.[START_FNMOMENT]/60, '12/30/1899 00:00:00'),
DATEADD(mi, A.[CLEAR_FNMOMENT]/60, '12/30/1899 00:00:00') ),0) AS DURation,
CONVERT(datetimeoffset, SYSUTCDATETIME()) AT TIMEZONE 'US Eastern Standard Time';
DECLARE @offset char(6) = RIGHT(@datetime, 6);
SELECT @datetime, @offset, SWITCHOFFSET(SYSUTCDATETIME(), @offset)
from [dbo].[SCH_ALARM_INST] A
Inner Join [dbo].[SCH_ALARM] B
ON A.[SCH_ALARM_SK] = B.[SCH_ALARM_SK]
Inner Join [dbo].[EMP] C
ON C.[EMP_SK] = A.[EMP_SK]
where DATEADD(day, -2, [NOM_DATE]) between '2018-06-08 00:00:00.000' and '2019-06-08 00:00:00.000'
and A.[SCH_ALARM_SK] IN ('-988632205052')

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.