How to convert a VARCHAR DD/MM/YYYY H:MM:SS AM into datetime and then to Json date?

Question

So I know this question is crazy. I just spent days trying to solve this but i think it's impossible.

I have a table with a CreatedDate as a VARCHAR field.

10/7/2019 7:12:59 AM

What I need to to is to convert this date into those dates we see on MongoDB or Json files. It should be like this:

2019-07-04T15:35:41.958UTC-03:00

is there a way to do this? I tried to convert to datetime, I tried substring, left right. I can force the -03:00 at the end.

SELECT CONVERT(VARCHAR(33), getdate(), 126)

With this I can convert a datetime field and thats the problem.

Answer 1

somewhat old but still ...

https://stackoverflow.com/questions/10735447/is-there-a-way-to-convert-a-varchar-to-datetime-in-sql-server-2008

convert(datetime,'24/05/2012 09:56:06',103)

set your column containing the varchar as the second parameter - 103 is for the --/--/---- format ..

I tested above command against a SQL Server 2014 and it works when you omit leading zeroes - and also with AM / PM addition so perfect for your case

The only thing that needs some more work is your desired addition of the timezone, but I highly suggest adding that only for display, not in the actual database