Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> partially specified dates

partially specified dates

From: Kristian <nospam-kvl_at_earthlink.net>
Date: 1998/02/13
Message-ID: <6c1ura$f2@bolivia.earthlink.net>#1/1

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,

Received on Fri Feb 13 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US