Post reply

How can I convert varchar to date for mixed date format?

  • May 29, 2009 at 12:56 am

    #93571

    Hello, I have a field with mixed date format some are YYYYMMDD, some are DDMMYYYY, some are MMDDYYYY and some are DDMONYYYY. Can you please let me know if I can use one SQL statement to convert them to datetime? Thank you.

    Wenjing

  • May 29, 2009 at 3:51 am

    #1001953

    Wenjing Lin (5/29/2009)

    Hello, I have a field with mixed date format some are YYYYMMDD, some are DDMMYYYY, some are MMDDYYYY and some are DDMONYYYY. Can you please let me know if I can use one SQL statement to convert them to datetime? Thank you.

    Wenjing

    Hi,

    In the table you having display in the YYYYMMDD format, so that the data like

    20090529 or 2009/05/29 or 2009-05-29 or any other intermediate?

    /*statement yet to complete*/

    Mean while try this,

    create table #temp

    (

    date1 varchar(15),

    format1 varchar(10)

    )

    insert into #temp

    select '20090529',''

    union

    select '28122009',''

    union

    select '05272009',''

    union

    select '01012009',''

    union

    select '12122008',''

    union

    select '26MAY2009',''

    --select * from #temp

    update #temp

    set format1 = case when left(date1,4)between '1900' and '2200' then 'YYYYMMDD'

    when left(date1,2)between '01' and '31'

    and substring(date1,3,2)between '01' and '12' then 'DDMMYYYY'

    when left(date1,2)between '01' and '12'

    and substring(date1,3,2)between '01' and '31' then 'MMDDYYYY'

    when date1 like('%[A-Z]%')then 'DDMMYYYY' end

    select convert(datetime,date1,(case when format1 = 'YYYYMMDD'then 112

    when format1 = 'DDMMYYYY'then 113

    when format1 = 'MMDDYYYY'then 110 end))date1

    from #temp

    ARUN SAS

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

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