Popular White Paper On This Topic

Looks like you loaded a date into a date format but failed to provide a date
picture format that oracle understands. I ran into a situation like this and
ended up having to fix my data into a an acceptable date format.
The integer number is a result of a year, month, day but without knowing the
sequence m/d/y or d/m/y or y/m/d it's easier to go back to the source and
reload after defining that format.

These look like Julian date formats. YYDDD. So, 89186 would be the 186th day of 1989. There are ways to code up "case" statements that will decipher them if Oracle does not have a built-in function to take care of this. The only hard part of coding it is to remember that Leap Years have 366 days. You can use something like"

It's almost as easy to build a table of Julian dates which can then be queried with the YYDDD key to return the MM/DD/YY date you desire. If you can build the table in Excel and upload it to your database, that's one way to go.

It's clunky but it will do the job.

I know there are ways to convert Julian dates to MM/YY/DDDD using Oracle but cannot remember exactly how and don't have my book available. It depends on your version of Oracle and whatever software (we use TOAD) you use to access the data.

Mark may be right about it being better to format the dates properly coming out of the source than trying to format them coming into Oracle. If that's not an option due to lack of control over the source format, you can still do the reformatting if you can determine the source format.

The 3 dates you provided look like Julian date format to me. My guess would be that 89186, 93119, 92182 are the 186th day of 1989, 119th day of 1993, and the 182nd day of 1992. These would be reasonable DOBs for young adults, hire dates for lon-term employees, issue date for certain insurance policies, etc.

If the dates are in that format, the corresponding Oracle date format mask would be 'RRDDD'. This will supply 19 as the 1st 2 digits of the year for the 3 example you gave and anything else starting with 50 through 99. For the rest (00 - 49), it will supply 20.

Oracle, somewhat confusingly, uses the term Julian in reference to the 'J' date format where 'J' is the number of days since January 1, 4712 BC. If your sample dates were in this format, they would be valid, but probably not reasonable for your application (sometime during the 44th century BC).

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.