Avatar of Mark
Mark
 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?
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Mark

8/22/2022 - Mon
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

Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.