Re: varchar to date

Nicholas Carey wrote:
>
> 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.
> --
Or...just use a DATE datatype and be done with it.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.