How can I convert a JSON date with timezone to a SQL Server datetime?

Tarzan Published at Dev

260

Tarzan

I saved the output of a JSON object in a CSV file. I want to import the data into SQL Server. I have tried to cast the JSON date column to a SQL Server datetime data type.

select cast('2009-06-18T16:44:20+0000' as datetime)

This causes an error to be raised: Conversion failed when converting date and/or time from character string.

How can I convert a JSON date with timezone to a SQL Server datetime?

Paweł Dyl

As far as I know, SQL Server recognizes timezone with colon. You have to reformat timezone part as follows:

SELECT CONVERT(datetime2, STUFF('2009-06-18T16:44:20+0000', 23, 0, ':'))

According to MSDN ISO 8601 has YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm] format.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-10-25

Comments

0 comments

How can I convert a string into a DateTime for a specific timezone, preserving the date exactly is it comes for that timezone?

How to convert string contain timezone to datetime sql server

How can I convert all DateTime properties to a different TimeZone in a collection?

How can I batch convert timezone of DATETIME entries?

How I can convert a DateTime with timezone to a Unix TimeStamp ? C#

How can I convert a JSON date with timezone to a SQL Server datetime?

How can I convert a JSON date with timezone to a SQL Server datetime?

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Unable to use switch toggle for dark mode in material-ui

grouping by column variables and appending a new variable based on condition

BigQuery - concatenate ignoring NULL

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Python Read Directory And Output to CSV

Remove adjacent duplicates in linked list in C

Angular 8. Unknown amount of http.get requests in array to call, must be sequential, what to use

How to keep curl session alive between two php processes?

Limit number of characters in uitextview

JMeter: Why get error when try to save test plan

Always setting the text cursor on Textbox

MTKView Displaying Wide Gamut P3 Colorspace

Vector input in shiny R and then use it

How to implement an authentication method using Spring Boot and JPA?

Laravel getting value from another table using eloquent

org.springframework.web.client.HttpClientErrorException: 401 null

When I click any button in my view page the form is submitted