-1

I'm a bit rusty and seem to have completely forgot how to do this. I am receiving data and the datatime column is in string format char(12).

Example : 201411061900

How would I write the query (view) to convert that to DateTime?

I've used CONVERT (datetime, dbo.KWH.mr_dtm,120), but I get the error Conversion failed when converting date and/or time from character string.

I haven't had much luck. Thank you very much in advance.

If you want to suggest -5 time zone also, I wouldn't mind. :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Michael Brown
  • 47
  • 1
  • 6

1 Answers1

0

Magnus answered my question. I wanted to post it here incase it helps anyone in the future. Here is the link to the answer.

There is no output format identifier (like for example 120) that lets you convert a char(12) into a datetime in SQL Server. But you could use the Substring function:

Select Cast(Substring(dbo.KWH.mr_dtm,1,8) + ' ' + Substring(dbo.KWH.mr_dtm,9,2)+':'+ Substring(dbo.KWH.mr_dtm,11,2) as DateTime)

Thank you to everyone! It is very much appreciated.
Oh, and this post help me solve my timezone setup.

Convert Datetime column from UTC to local time in select statement

Many thanks to Michael Goldshteyn for the timezone convert.

Community
  • 1
  • 1
Michael Brown
  • 47
  • 1
  • 6