Avatar of Alexandre Takacs
Alexandre Takacs
Flag for Switzerland asked on

Date time conversion / storage in SQL server

Hi

I'm getting via an API date time values in the format 2019-01-20T10:16:49Z

What's my best approach to store them in SQL server ?

I have tried to define a DATETIME column and insert via CONVERT(DATETIME, '2019-01-20T10:16:49Z', 102) )  but it fails...

I'm sure it muss be pretty easy but google isn't my friend so far...
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Alexandre Takacs

8/22/2022 - Mon
Mark Edwards

In your string date/time, the "Z" of course stands for the 24-hour clock military "ZULU" time, which is the same as GMT except that "Z" does NOT observe daylight savings time.
You'll need to convert if you want local time.
Also, the "T" isn't going to be recognized by SQL Server, so you'll need to replace it with a space.
So replace the "T" with a space first and get rid of the "Z" and convert to date/time, then calculate local time if you want local.
Alexandre Takacs

ASKER
Thanks

So this would not be something that SQL server could interpret natively?
ASKER CERTIFIED SOLUTION
Snarf0001

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gerald Connolly

Hey Mark
“... ZULU time which is the same as GMT, but Z doesn’t observe daylight saving ...”
The GMT Timezone doesn’t observe daylight saving either!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Alexandre Takacs

ASKER
If not, and it is an older format, then 127 is the format that recognizes "Z":
select convert(datetime, '2019-01-20T10:16:49Z', 127)

Thanks - that did the trick !

As a "general" rule, it's best to store dates in UTC time.

Hmm do we actually have control how SQL server stores date/time values? We can format it whichever way we see fit but IMHO the storage is beyond our control...