Convert datetime to UTC string format

GNiessen
GNiessen used Ask the Experts™
on
I need to convert a Date Time from our sql server to a date time format that includes UTC offset like "2018-10-05T12:00:00-05:00" that supports daylight savings time.  I am sure there is a function to do this, but I can't seem to ask google the right question to find it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Convert? Can your rephrase your question?

Depending on the use-case use the appropriate data type:  DATETIMEOFFSET.
Using the SYSDATETIMEOFFSET() function to get the current value.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
I think this is what you are looking fo
 SELECT SYSDATETIMEOFFSET();  

Open in new window

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


Example, my machine is Sydney AUS - just gone to daylight saving...

 SELECT SYSDATETIMEOFFSET();  

 returns : 2018-10-08 23:51:01.5642041 +11:00


The other method is to use "AT TIME ZONE" see : https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017  But your SQL Server needs to be 2016

example :

SEECT convert(varchar(30), getdate() at time zone 'AUS Eastern Standard Time',126)

returns : 2018-10-08T23:51:01.567+11:00

And almost forgot one of my favourites - FORMAT() - since SQL2012 :

select format(getdate(),'yyyy-MM-ddTHH:mm:sszzz')

While my examples above use GETDATE() - you would use your date column.

Author

Commented:
Unfortunately the SYSDATETIMEOFFSET() is not the format I need.  And since we support Daylight Savings Time here, our offset changes from -05:00 to -06:00.  I was looking for a solution where I would not have to write my own function to manage the format.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

It seems you would need more complex expression:

SELECT SYSDATETIMEOFFSET(), LEFT(CONVERT(varchar(50), SYSDATETIMEOFFSET()), 10)+'T'+SUBSTRING(CONVERT(varchar(50), SYSDATETIMEOFFSET()), 12, 8)+RIGHT(CONVERT(varchar(50), SYSDATETIMEOFFSET()), 6)
ste5anSenior Developer

Commented:
What's the problem?

Currently its 2018-10-08 16:16:32.5182605 +02:00 here in Germany. The CEST is coded in the +02:00. In three weeks it will be 2018-10-08 15:16:32.5182605 +01:00. Then the CET is in the +01:00.

Unfortunately the SYSDATETIMEOFFSET() is not the format I need.
Can you imagine that more details would be helpful?
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Did you see all my post above ?

You dont have to write your own routine.

You can use (from above) the FORMAT() command :

select format(getdate(),'yyyy-MM-ddTHH:mm:sszzz')


And there were other suggestions above as well (depends on what version of SQL Server you are running)



BTW, what version of SQL are you using ?
ste5anSenior Developer

Commented:
btw, conversion for consumers is best done in the front-end..
@ste5an:  LOL
Currently its 2018-10-08  in Germany … in three weeks it will be 2018-10-08 ... What date will be in Germany in three years?

@Mark Wills:
Yes, if GNiessen uses SQL 2012 and higher then it should work (updated)
The question is what should be the time part displayed?  Local time or UTC?  The standard is Local time + offset.

BUT if this time format should be calculated from simple datetime column which contains just local time then you have uncertainty during the 1 hour period when Daylight saving changes to normal time.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Have you tried this where expression is the datetime field

TODATETIMEOFFSET ( expression , time_zone )  

mlmcc
ste5anSenior Developer

Commented:
pffft.. ;)

Author

Commented:
I do need to be able to pass in a datetime value.  Say for example:
MyDT = '2018-10-01 08:30:00'
And get the appropriate format back: '2018-10-01T08:30:00-05:00' and also supprt the daylight saving change so that '2018-12-24 08:00' comes back as '2018-12-24T08:00:00-0600'
ste5anSenior Developer

Commented:
Please again: more details please.

If we talk about a SQL Server backend, then use the DATETIMEOFFSET data type and store the correct value.

Cause a function date to date+offset is not trivial, cause it is prone to error. Because you need to take into account, that you cannot for sure tell, what time zone (offset) was used (meant) when the a date was entered. E.g. what should  be the output for 2018-10-28 02:30?

Author

Commented:
I am using using SQL Server 2014 and in the Central US timezone.  I don't want to have to figure out if it is CST or CDT, or -5 or -6.  I figure the system can figure that out if I ask nicely.  :-)

Author

Commented:
SYSDATETIMEOFFSET() only returns the current time in the appropriate offset.  So if I am needing the date time formatted for a record from 6 months ago, it is not directly useful.
@GNiessen: This is achievable by the FORMAT() function (see Mark Wills posts) except all cases which are falling into the one hour after the Daylight saving ends.

It seems you are using CDT time now.
What offset do you use for the local time 2018-11-04 01:30:00?  Such local time is ambiguous when you don't know whether it falls into CDT or CST.
Opposite problem does not happen hopefully. I mean 1 hour when the CST time changes to CDT which could produce non-existing local time.

Author

Commented:
@pcelba: Yes, I could use the Format() recommended by Mark Wills, if I knew how to get the local offset for the DateTime being formatted.  How can I find if the Offset at that date time should be -5 or -6 for our timezone?
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
There is a system view  (from SQL2014 as it turns out)  https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-time-zone-info-transact-sql?view=sql-server-2017

select * from sys.time_zone_info
where name like 'Central%Standard Time'
and current_utc_offset in ('-05:00','-06:00')

You will note a flag is set if currently in CDT  - that should be automatic (server settings)

Because of that, SQL "knows" what datetime a particular date belongs to. Try this example
select convert(varchar(30),getdate(),126) as [Date as at NOW]
      ,format(dateadd(month,-2,getdate()),'yyyy-MM-ddTHH:mm:sszzz') as [Date 2 months ago]
      ,format(dateadd(month,+2,getdate()),'yyyy-MM-ddTHH:mm:sszzz') as [Date in 2 months]

Open in new window

The only difference (apart from precision) is the time zone as denoted by the zzz of the Format() string.

But 12:50 PM will be 12:50 PM regardless of -06:00 or -05:00

Does that make sense ?

Author

Commented:
Thanks everyone for the quick response and various recommendations.  I picked the solution that looks the simplest to maintain on my end.
Just be aware of the above function accuracy. It does not handle properly the times which belong to the transition period between Daylight saving and Standard time. If you want to be accurate then you should check whether your data contain ambiguous local times or (less possibly) non-existing times.

Ambiguous are e.g. local times between 2018-11-04 01:00 and 2018-11-04 02:00. FORMAT() function handles them as Standard time whereas it could also be Daylight saving time.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial