How convert char ddmmyyyy to date dd.mm.yyyy?

Question

How convert char ddmmyyyy to date dd.mm.yyyy?

zdenek douda

SSC-Addicted

Points: 419
More actions
October 6, 2004 at 2:24 am

#182086

How can I do it on sql server 2000?
Thanks zdenek

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Well, this may be a bit tricky - not to produce the format, but the ddmmyyyy is not a valid date, so you can't use any convert styles. Also, you have to trust that your 'dates' are actually dates, else you'll probably get some junk in there as well...

Anyway, probably the most 'clean' way (ie most readable) is to just concatenate the parts and tack on a '.' in between. There are quite a few ways to get creative with different string functions in order to solve this.

Here's a solution:

select right('06102004', 4) +

'.' +

substring('06102004', 3, 2) +

'.' +

left('06102004', 2)

----------

2004.10.06

(1 row(s) affected)

/Kenneth

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

Another way:

DECLARE @Date CHAR(8)

SET @Date = '06102004'

SELECT

CONVERT(VARCHAR(10), CAST(RIGHT(@Date, 4) + '-' +

LEFT(@Date, 2) + '-' + SUBSTRING(@Date, 3, 2) AS DATETIME), 104)

Good Hunting!

AJ Ahrens

webmaster@kritter.net

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Or

STUFF(STUFF('06102004',5,0,'.'),3,0,'.')

and to type datetime

CAST(STUFF(STUFF('06102004',5,0,'.'),3,0,'.') as datetime)

Far away is close at hand in the images of elsewhere.
Anon.

Naren Bhat SSC Veteran Points: 224 More actions · Answer 4

declare @date char(8)

set @date = '06122004'

select convert(char(10), convert(datetime, substring(@date,3,2) + '/' + substring(@date,1,2) + '/' + substring(@date,5,4)), 104)

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

Great solution David! Never would have thought about stuff

Good Hunting!

AJ Ahrens

webmaster@kritter.net