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,

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)

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

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)