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

• Wenjing Lin

  SSC Enthusiast

  Points: 188

  More actions

  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

• arun.sas

  SSChampion

  Points: 11831

  More actions

  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