T-SQL to convert Datetime and DatetimeOffset to Zulu Format

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

Answer 1

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

Answer 2

It returns the server offset from UTC time.

See the examples:

https://docs.microsoft.com/en-us/sql/t-sql/functions/sysdatetimeoffset-transact-sql?view=sql-server-ver15

Answer 3

Thanks

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

Answer 4

This should work

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

Answer 5

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.

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

If you just want the current UTC datetime as an offset, you just need to do this:

SELECT CAST(SYSUTCDATETIME() AS datetimeoffset)

Answer 10

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;)

Answer 11

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, [e-mail]

Answer 12

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]

Answer 13

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.