CODEWITHSUNDEEP
sql-server-2005
del.ave
del.ave

Reputation: 1948

SQL Server String Parsing

I have SQL Server 2005 and all dates are stored using a DATETIME column type. The front-end application handles dates in a "yyyyMMdd hhmmss" format. I'm writing a set of SQL queries and stored procedures and was wondering if there is an easy way to convert this format to the standard SQL DATETIME. I did not code the front-end app so I cannot make any changes to it.

I have looked into CONVERT(), but none of the type codes match what I want. The closest one is 

CONVERT(DATETIME, '20101017' 112)

But that does not have the time component of the input. Any ideas? or do I have to write a SQL function to do that parsing and conversion.

Thank you,

Upvotes: 2

Views: 274

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135928

If you insert the colons into the appropriate places in your time, you can use style 120.

declare @d varchar(15)
set @d = '20101017 111428'

select CONVERT(DATETIME, stuff(stuff(@d,12,0,':'),15,0,':'), 120)

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171569

How about this:

declare @s as varchar(25)
set @s = '20101109 172054'
select @s, convert(datetime, SUBSTRING(@s, 1, 4) + '-' + SUBSTRING(@s, 5, 2) 
    + '-' + SUBSTRING(@s, 7, 3) + SUBSTRING(@s, 10, 2) + ':' 
    + SUBSTRING(@s, 12, 2) + ':' + SUBSTRING(@s, 14, 2), 20)

Upvotes: 1

Paul Sonier
Paul Sonier

Reputation: 39510

You might find this page to be useful (if you can get past the psychosis-inducing color scheme).

Upvotes: 1

Related Questions