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...
LVL 2
Alexandre TakacsCTOAsked:
Who is Participating?
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.

Mark EdwardsChief Technology OfficerCommented:
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 TakacsCTOAuthor Commented:
Thanks

So this would not be something that SQL server could interpret natively?
Snarf0001Commented:
SQL should be able to interpret fine.  And unless you're on a pretty old version of SQL, you shouldn't even have to specify the format.

This should work fine:
select convert(datetime, '2019-01-20T10:16:49Z')

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)

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

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
Gerald ConnollyCommented:
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!
Alexandre TakacsCTOAuthor Commented:
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...
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
SQL

From novice to tech pro — start learning today.