How to convert varchar to datetime

Question

I have a varchar column header (Prod_time) in the [YYYYmmdd hhmmss] format that I am trying to convert to datetime. I need to be able to pull data from a certain number of days, and I would like to be able to convert the varchar to datetime to facilitate this.

Is there a way to convert varchar to datetime? No special formatting of the datetime needed, only a data type conversion.

Answer 1

You need to force a couple characters in here so that the convert function knows how to deal with this. We can use STUFF for this pretty easily. This works given the provided string format.

declare @SomeChar varchar(20) = '20170216 100903'

select CONVERT(datetime, STUFF(STUFF(@SomeChar, 12, 0, ':'), 15, 0, ':'))

If at all possible you should consider converting the datatype to a datetime. It eliminates this kind of hassle and also prevents invalid values.