Date Function

Question

Post reply

Date Function

Trybbe

Hall of Fame

Points: 3967
More actions
February 4, 2009 at 3:25 am

#206274

Hi
How dow I convert this date AUG2007 to 200708.
Select Distinct Replace(Convert(Numeric, [Month], 112), '/', '') as Period
From PTX_TERMINATIONS
I tried this code and several other combinations but I'm not winning.
Please help

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

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

try this:

DECLARE @dt VARCHAR(10)

SELECT @dt = 'AUG2007'

SELECT CONVERT(VARCHAR(6),CAST(LEFT(@dt,3) + ' ' + '01 ' + RIGHT(@dt,4) AS DATETIME), 112) AS DT

/*

DT

------

200708

*/

.

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 2

You don't actually need the day...declare @dt varchar(10)

set @dt = 'AUG2007'

select year(convert(datetime,@dt))*100+month(convert(datetime,@dt)) as period -- int

select convert(char(6),Convert(datetime, @dt), 112) as Period -- char(6)

Derek

gary.proctor Mr or Mrs. 500 Points: 580 More actions · Answer 3

This is the way I would do it, but I am sure there are many more ways:

SELECT CONVERT(CHAR(6),CONVERT(SMALLDATETIME, GETDATE()), 112)

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

[font="Verdana"]Mine would be:

selectconvert(varchar(6), getdate(), 112)

[/font]

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 5

gary.proctor (2/4/2009)
This is the way I would do it, but I am sure there are many more ways:
SELECT CONVERT(CHAR(6),CONVERT(SMALLDATETIME, GETDATE()), 112)

Bruce W Cassidy (2/4/2009)
[font="Verdana"]Mine would be:
selectconvert(varchar(6), getdate(), 112)
[/font]

The only thing is that thatok asked

thatok (2/4/2009)
How dow I convert this date AUG2007 to 200708.

So the key point I was making is that CONVERT will do the conversion without the need for a day number!

Derek

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 6

[font="Verdana"]Okay. How about:

select convert(varchar(6), convert(datetime, 'AUG2007'), 112)

[/font]