dateformat little fast reply

Question

Post reply

dateformat little fast reply

samsql

Hall of Fame

Points: 3044
More actions
July 25, 2008 at 4:17 am

#122679

hi i want to convert 280908(ddmmyy) to
2008-09-28 00:00:00.000

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

If the data is always in this format this should work.

declare @tst Varchar(6)

Set @tst = '280908'

Select cast(substring(@tst,3,2) + '/' + left(@tst,2) + '/' + right(@tst,2) as datetime)

What is the datatype of the column you are working with?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT CONVERT(DATETIME, STUFF(STUFF('280908',3,0,'/'),6,0,'/'), 3)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Here's an alternative...

select cast(stuff(stuff('280908', 5, 0, '/'), 3, 0, '/') as datetime)

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

tosscrosby SSCoach Points: 18045 More actions · Answer 4

RyanRandall (7/25/2008)
Here's an alternative...
select cast(stuff(stuff('280908', 5, 0, '/'), 3, 0, '/') as datetime)

I'm still on 2000 so I though you guys were pulling somebody's chain with "stuff"! But lo and behold, a little research into BOL 2005 on the web and there it was, references to it and how to use it. Learned something new today...can't wait to upgrade! Thanks guys! :satisfied:

-- You can't be late until you show up.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Hi Terry

You don't need to hold your breath - try the code in SQL Server 2k.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

RyanRandall (7/25/2008)
Here's an alternative...
select cast(stuff(stuff('280908', 5, 0, '/'), 3, 0, '/') as datetime)

Is it friday or have we been here before?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

RyanRandall SSChampion Points: 13623 More actions · Answer 7

Chris Morris (7/25/2008)
RyanRandall (7/25/2008)
Here's an alternative...
select cast(stuff(stuff('280908', 5, 0, '/'), 3, 0, '/') as datetime)
Is it friday or have we been here before?

Yep - You sniped me by a few seconds 🙂

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

tosscrosby SSCoach Points: 18045 More actions · Answer 8

Chris Morris (7/25/2008)
Hi Terry
You don't need to hold your breath - try the code in SQL Server 2k.
Cheers
ChrisM

Son-of-a-gun...I wonder what else I've been missing?? That just proves again that no matter how much you think you know, there's even more that you don't. Just another reason to frequent this site. Thanks Chris, and of course, Ryan (even though Chris posted first!) :Whistling:

-- You can't be late until you show up.