SQL Server String Parsing

Question

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,

Joe Stefanelli · Accepted Answer · 2010-11-09 17:24:55Z

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)

answered Nov 9, 2010 at 17:24

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Sonier · Accepted Answer · 2010-11-09 17:13:49Z

1

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

answered Nov 9, 2010 at 17:13

Paul Sonier

39.6k3 gold badges81 silver badges118 bronze badges

2 Comments

del.ave Over a year ago

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

Paul Sonier Over a year ago

@del.ave: yeah, it seemed very example-intensive (and eye-bleedingly annoying with the formatting!).

D'Arcy Rittich · Accepted Answer · 2010-11-09 17:20:54Z

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)

answered Nov 9, 2010 at 17:20

D'Arcy Rittich

172k41 gold badges297 silver badges287 bronze badges

Collectives™ on Stack Overflow

SQL Server String Parsing

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related