convert string to datetime

Mark
Mark used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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)
Top Expert 2010

Commented:
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

Top Expert 2012

Commented:
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

Top Expert 2012
Commented:
Actually this would be more precise:
SELECT CONVERT(datetime, STUFF(STUFF(STUFF(YourColumnNameGoesHere, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'), 120)

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial