date conversion

  • table contains date like

    071009(mmddyy),42209 (mddyy)

    how to convert this in datetime?

  • Is the day portion always 2 characters, in other words you don't have 1109 (mdyy)?

  • only

    mmddyy or mddyy

    day always 2 char

  • declare @temp as table (txtdate varchar(10))

    insert into @temp select '071009' union select '42209'

    select

    CONVERT(datetime,

    case

    when LEN(txtdate) = 6 then STUFF(STUFF(txtdate, 3, 0, '-'), 6, 0, '-')

    else '0' + STUFF(STUFF(txtdate, 2, 0, '-'), 5, 0, '-')

    end, 10

    )

    from @temp

  • Allister Reid (6/9/2009)


    declare @temp as table (txtdate varchar(10))

    insert into @temp select '071009' union select '42209'

    select

    CONVERT(datetime,

    case

    when LEN(txtdate) = 6 then STUFF(STUFF(txtdate, 3, 0, '-'), 6, 0, '-')

    else '0' + STUFF(STUFF(txtdate, 2, 0, '-'), 5, 0, '-')

    end, 10

    )

    from @temp

    You can remove your case statement by doing the following:

    SELECT

    CONVERT(datetime,

    STUFF(STUFF(RIGHT('0'+txtdate,6),5,0,'-'), 3, 0, '-'),

    10)

    from @temp

    /* Anything is possible but is it worth it? */

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

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