locked
T-SQL to convert Datetime and DatetimeOffset to Zulu Format RRS feed

  • Question

  • How to convert datetime and datetimeoffset to zulu format in Sql server 2014 and above

    How does the function SYSDATETIMEOFFSET() works - Does it calculate the offset of the server in which SQL Server is installed w.r.t to UTC timezone . Or how does it works internally

    Friday, August 14, 2020 3:15 PM

All replies

  • Hi

    Do you need to change time zone offset

    DECLARE @CurrentDateTime DATETIME2; 
    SET @CurrentDateTime = Getdate(); 
    SELECT Todatetimeoffset (@CurrentDateTime, '+08:00') AS 
           CURRENT_DATE_TIME_OFFSET_CHANGE;

    Thanks and regards

    Friday, August 14, 2020 3:51 PM
  • Friday, August 14, 2020 4:05 PM
  • Thanks

    How do we convert datetime/datetimeoffset  to zulu format . Please suggest 

    Sunday, August 16, 2020 10:47 PM
  • This should work

    select convert(varchar(51), GETUTCDATE(),127)+'Z'

    Monday, August 17, 2020 12:48 AM
  • Hi SubhadipRoy,

    Please refer to below sql:

    select DATEADD(hour,DATEDIFF (hour, GETDATE(), GETUTCDATE()),MyLocalDateTime) as GmtDateTime

    I found a similar issue, you can refer to:SQL Convert to GMT DateTime

    Best Regards 
    Echo   


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    • Edited by Echo Liuz Monday, August 17, 2020 7:40 AM
    Monday, August 17, 2020 7:39 AM
  • This is more difficult than you think, if you are talking about converting past times to UTC time.  You would have to know the offset on the date, including daylight savings time.

    If you just want now in UTC, then use SYSUTCDATETIME:  https://docs.microsoft.com/en-us/sql/t-sql/functions/sysutcdatetime-transact-sql?view=sql-server-ver15


    Monday, August 17, 2020 12:50 PM
  • Hi SubhadipRoy,
    Has your problem been solved? If it is solved, please mark the point that you 
    think is correct as an answer. This can help others who encounter similar problems.

    Best Regards 
    Echo   

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 18, 2020 5:56 AM
  • How do we convert datetime/datetimeoffset  to zulu format . Please suggest

    select convert(varchar(51), GETUTCDATE(),127)+'Z'  is not giving the expected value .

    When using SELECT SYSDATETIMEOFFSET()  and trying to convert to zulu format using the convert(varchar(51), GETUTCDATE(),127)+'Z' 

    offset value is not getting honoured

    Tuesday, August 18, 2020 4:39 PM
  • If you just want the current UTC datetime as an offset, you just need to do this:

    SELECT CAST(SYSUTCDATETIME() AS datetimeoffset)

    Tuesday, August 18, 2020 4:46 PM
  • Hi SubhadipRoy,

    This thread has lots of replies without any resolution.

    If you want to get an answer, you need to provide a minimal reproducible example:
    (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
    (2) What you need to do, i.e. logic.
    (3) Desired output based on the sample data in #1 above.
    (4) Your SQL Server version (SELECT @@version;)
    Tuesday, August 18, 2020 4:48 PM
  • Did you ever tell us what Zulu format is?

    Can you give us some examples of input and the expected result?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, August 18, 2020 9:15 PM
  • How do we convert datetime/datetimeoffset  to zulu format .

    Zulu isn't a "format", it's the military naming for UTC +0 timezone, nothing else and you got already example for UTC.

    https://www.timeanddate.com/time/zones/z


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 19, 2020 5:45 AM
  • Hi SubhadipRoy,

    Do you have any updates?

    Has your problem been solved? If it is solved, please mark the point that you 
    think is helpful as an answer. This can help others who encounter similar problems.   

    Best Regards 
    Echo   


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    • Edited by Echo Liuz Thursday, August 20, 2020 5:25 AM
    Thursday, August 20, 2020 5:25 AM