Re: varchar to date

[Quoted] On 28 Nov 2001, "Matt B." <gtimatt_at_home.com> spake and said:

> "longinus" <longinus_at_zeus.polsl.gliwice.pl> wro
> If you do this:
>
> to_date(min(end_date),'DD.MM.YYYY'))
>
> ...it will work w/o error because it'll find the mininum
> END_DATE first, which you know to be 15.10.2001 and since
> this is in the proper format it will not error.

Actually, it won't work correctly, since DD.MM.YYYY doesn't collate properly. The date '01.01.2030' will collate before '15.10.2001' -- probably not what the original poster wants.

You need to clean the data first. You might also consider revisiting the format in which you are storing the date. ISO 8601, _Standard for the Representation of Date and Time_, provides date/time formats that will collate properly. Dates are represented as 'YYYY-MM-DD'. The '-' separator may optionally use '.' as a separator.

Another thing to consider is a change to the schema. Put a domain constraint on the column to enforce valid date formats. Something like this (assuming the ISO 8601 format:

  create table foo
  ( id int not null primary key ,     dtExpires varchar(10) not null

      check( '####-##-##' = translate( dtExpires ,
                              '0123456789' ,
                              '##########'
                              )
        and substring(dtExpires,1,4) between '1900' and '9999'
        and substring(dtExpires,6,2) between   '01' and   '12'
        and substring(dtExpires,9,2) between   '01' and   '31'
        and 1 = case
                  when to_number(substring(dtExpires,6,2))
                         in (04,06,09,11)
                    then case
                           when to_number(substring(
                                  dtExpires,9,2
                                  )) between 01 and 30
                           then 1
                           else 0
                         end
                  when to_number(substring(dtExpires,6,2)) = 02
                    then case
                           when to_number(
                               substring(dtExpires,9,2)
                               )
                             between 01
                                 and case
                                     when mod(to_number(
                                       substring(
                                         dtExpires,
                                         1,4),
                                       4
                                       )) = 0
                                      and mod(to_number(
                                       substring(
                                         dtExpires,
                                         1,4),
                                       400
                                       )) not in (100,200,300)
                                     then 29
                                     else 28
                                     end
                           then 1
                           else 0
                         end
                  when to_number(substring(dtExpires,6,2))
                         in ( 01,02,03,05,07,08,10,12)
                    then case
                           when to_number(substring(
                                  dtExpires,9,2
                                  )) between 01 and 31
                           then 1
                           else 0
                         end
                  else 0
                end
        )

The above check (domain) constraint should enforce a valid ISO- 8601 date, including correct gregorian calendar leap year rules.

--