Date Function

  • Trybbe

    Hall of Fame

    Points: 3967

    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

  • jacob sebastian

    SSChampion

    Points: 11812

    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: 8633

    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

    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: 12607

    [font="Verdana"]Mine would be:

    select convert(varchar(6), getdate(), 112)

    [/font]

  • StarNamer

    SSCrazy Eights

    Points: 8633

    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:

    select convert(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: 12607

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

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

    [/font]

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

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