Convert datetime to UTC string format

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.
GNiessenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
0
Mark WillsTopic AdvisorCommented:
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.
0
GNiessenAuthor 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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pcelbaCommented:
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)
0
ste5anSenior DeveloperCommented:
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?
0
Mark WillsTopic AdvisorCommented:
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 ?
1
ste5anSenior DeveloperCommented:
btw, conversion for consumers is best done in the front-end..
0
pcelbaCommented:
@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.
0
mlmccCommented:
Have you tried this where expression is the datetime field

TODATETIMEOFFSET ( expression , time_zone )  

mlmcc
0
ste5anSenior DeveloperCommented:
pffft.. ;)
0
GNiessenAuthor 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'
0
ste5anSenior DeveloperCommented:
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?
0
GNiessenAuthor 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.  :-)
0
GNiessenAuthor 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.
0
pcelbaCommented:
@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.
0
GNiessenAuthor 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?
0
Mark WillsTopic AdvisorCommented:
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 ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GNiessenAuthor Commented:
Thanks everyone for the quick response and various recommendations.  I picked the solution that looks the simplest to maintain on my end.
0
pcelbaCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.