asked on

convert string to datetime

I have a varchar string e.g. '20110118200736' stored in a database column. This is a string representation of YYYYmmddHHMMSS. I'm not sure why the table designer made this a string instead of a datetime, but that's what I have to work with.

Is there a simple way to convert this to a datetime without parsing the whole darn thing? simply assigning it to a datetime doesn't work. convert(datetime,stringval) doesn't work. I can get the date component using datetimeVal = left(stringval,8), but that leaves me to parse the time component.

Any ideas?

lludden

Best I can come up with is:
DECLARE @T varchar(100) = '20110118200736'

SELECT CAST(SUBSTRING(@t,1,8) + ' ' + Substring(@T,9,2) + ':' + Substring(@t,11,2) + ':' + SUBSTRING(@t,13,2) as datetime)

Patrick Matthews

Two other ways I was working on in the meantime:

DECLARE @var varchar(50)
SET @var = '20110118200736'

SELECT CONVERT(datetime, LEFT(@var, 4) + '-' + SUBSTRING(@var, 5, 2) + '-' + SUBSTRING(@var, 7, 2) + 
    ' ' + SUBSTRING(@var, 9, 2) + ':' + SUBSTRING(@var, 11, 2) + ':' + RIGHT(@var, 2))

SELECT CONVERT(datetime, LEFT(@var, 8)) + 
    CONVERT(datetime, SUBSTRING(@var, 9, 2) + ':' + SUBSTRING(@var, 11, 2) + ':' + RIGHT(@var, 2))

Open in new window

Anthony Perkins

This should do it:
SELECT CONVERT(datetime, STUFF(STUFF(STUFF(YourColumnNameGoesHere, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'))

This is how I tested it:

DECLARE @Dat AS varchar(14) = '20110118200736'

SELECT CONVERT(datetime, STUFF(STUFF(STUFF(@Dat, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'))

Output:
2011-01-18 20:07:36.000

Open in new window

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark

ASKER

Wow, I've never used STUFF. It hurts my brain to figure out what you mean, but that solution is more consise, and it works.