Post reply

convert column with numbers to a specific date format

  • January 18, 2012 at 12:22 am

    #1434996

    @ VIG: Received error message

    'Invalid length parameter passed to the LEFT or SUBSTRING function.'

  • January 18, 2012 at 12:40 am

    #1435003

    mic.con87 (1/18/2012)

    @ VIG: Received error message

    'Invalid length parameter passed to the LEFT or SUBSTRING function.'

    Probably there is data less than 7 characters

    Give me an example of such data, pls

  • January 18, 2012 at 12:45 am

    #1435007

    There are 0's in the data set when no data was found. The two distinct character lengths are 8 and 1

  • January 18, 2012 at 1:35 am

    #1435025

    try this

    with

    q as (select CurrentDateFormat

    ,case when CurrentDateFormat=0

    then '19000101'

    else right(CAST(CurrentDateFormat as varchar(8)),4)

    + left(right(CAST(CurrentDateFormat as varchar(8)),6),2)

    + left(CurrentDateFormat,len(CAST (CurrentDateFormat as varchar(8)))-6)

    end dt

    from #Dates d

    )

    select CurrentDateFormat,cast(case when ISDATE(dt)=1 then dt else '19000101' end as datetime)

    from q

  • January 18, 2012 at 1:43 am

    #1435029

    Works great! Thanks for the alternative method:-)

Viewing 5 posts - 16 through 19 (of 19 total)

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