Re: partially specified dates

Kristian wrote:
>
> I'm loading data into my DB that contains dates. Most (95%) of these dates
> are specified as MM/DD/YYYY, but some have only MM/YYYY or even just YYYY.
> My problem is that even if I insert the dates with the appropriate format
> strings, they will be 'padded' in a way that will create "false" information
> when I pull the dates out of the DB.
>
> Example: Date 02/1891 when inserted as TO_DATE('02/1891','MM/YYYY') and then
> pulled out of the DB with TO_CHAR(FIELD,'MM/DD/YYYY') will result in
> "02/01/1891".
>
> This gives the illusion that we actually know the day, when we really don't.
>
> The problem is that my view system can't know that "01" sometimes means
> "unknown", neither can I do anything at the SQL level, since most dates will
> be fully specified, and thus should be pulled out in the manner specified
> above.
>
> I'm trying to find a way whereby the select clause can get the "precision"
> of the date somehow, or ideally, format the date correctly automatically.
> Does anyone have any ideas?
>
> Thanks,
>
> -- Kristian

My suggestion would be to maintain an extra data item containing an indicator to the precision (say, 'Y' for year, 'M' for month) and use a query view similar to:

select ...

      ,to_char(<date_col>,decode(<date_ind>
                                ,'Y','YYYY'
                                ,'M','MM/YY'
                                    ,'DD/MM/YY')) DATE_FIELD
       ...

Note that the <date_ind> column may be left null for the majority case, when the precision is known to the day.
An extension of this method can also be used for DATE datatypes which may or may not contain a time element.

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards, Guards"