Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> partially specified dates
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,