How can I reliably convert YYYYMMDDhhmmss<offset> to a date/time?

Question

I have a data source where the dates come back like this: 20150614140520-0500

How can I display this better? It doesn't actually have to be converted technically, this is just for reporting. Ideally it would output 2015-06-14 2:05 PM (EST) but I also want the statement to be as simple as possible so the timezone offset and 12hr time are optional.

I tried CAST ('20150614140520-0500' As Date) but it returns null

I also tried CAST (LEFT('20150614140520-0500',14) As Date) as well as some other variants without luck.

Answer 1

You have to take the first 14 characters, from the example above:

print convert(datetime,stuff(stuff(stuff(LEFT('20150614140520-0500',14) , 9, 0, ' '), 12, 0, ':'), 15, 0, ':')) 

Answer 2

Please try this:

convert(datetime,stuff(stuff(stuff(datevalue, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')) ConvertedDate

As suggested by Dinesh Kumar Rajendran in his blog: https://rdineshkumar.wordpress.com/tag/how-to-convert-yyyymmddhhmmss-to-datetimedatetime-in-sql-server/