Date time conversion / storage in SQL server
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...
Chief Technology Officer
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.
“... ZULU time which is the same as GMT, but Z doesn’t observe daylight saving ...”
The GMT Timezone doesn’t observe daylight saving either!
CTO
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...
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions