Re: Advice on date column?

Bruce Bristol wrote:
>
> Hello,
>
> I'm fairly new to my additional DBA responsibilities and need some
> advice.
>
> I'm going to be adding, removing and possibly changing some columns on
> our tables. This affords me the opportunity to add century to a column
> that we currently have a yymmdd date stored in.
>
> The column's type is number(6).
>
> The data we'll always receive from our customer will be in the yymmdd
> format, even on/after 2000.
>
> For sorting purposes, I can increase the column to a number(8) and add
> the century in our programs, or perhaps an insert trigger.
>
> Should I do this, or just change the column type to 'date'? If I do
> change the column type to date, how do I make sure that programs can
> access it by yymmdd or even yyyymmdd?
>
> If access can still be by yymmdd, will oracle think 000101 is 1900 or
> 2000?
>
> Thank you!
>
> -Bruce

Bruce,

You can certainly change the column datatype to DATE and then users can retrieve the date in YYMMDD format by using the TO_DATE function. If you try to search the table for rows where the date = 000101, it will return instances where the date is Jan 1, 1900 AND where the date is Jan 1,2000.

A benefit of changing the column type to DATE will be that you will be assured that only VALID dates are stored in the column.

If you do not want to change the oclumn type to DATE, then you may want to put a trigger on the table that checks for the column values to ensure that they are valid dates.  

-- 
Ashok F. Kapur    | Galaxy Latin America |
Project Engineer  | (954)958-3373        |
afkapur_at_ccgate.hac.com