2

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,

Improve this question

3 Answers 3

Reset to default
3

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Improve this answer

2 Comments

Thanks that page does contain a lot of examples indeed. It seems that I'm going to have to use string functions to substring and/or insert "-" and ":".
@del.ave: yeah, it seemed very example-intensive (and eye-bleedingly annoying with the formatting!).
1

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)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.