Register today for Oracle CloudWorld. October 17th - 20th, Las Vegas

Register now

Hillel Cooperman, Senior VP of User Experience Design, has a message for you on Oracle CloudWorld

Watch now
date_diff does not appear to work on dates past 01/01/1970 — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

date_diff does not appear to work on dates past 01/01/1970

Accepted answer
edited Dec 20, 2017 11:09AM in Reporting & Analytics for B2C Service 3 comments


Hi All,

As many will know, for a long time the date and date/time fields in OSvC would not accept dates prior to 01/01/1970 due to them using UNIX time and seemingly not allowing negative values. Therefore, no dates prior to the UNIX epoch could be stored. This was somewhat fixed a little while back with the introduction of date/time fields as Custom Objects that allow date/time prior to the UNIX epoch. This means Date of Birth can now effectively be stored.

However, I discovered yesterday that the date_diff function stops calculating the date difference when the date is prior to 01/01/1970, and every date prior to this time outputs the same value regardless whether it is 29/12/1969 or 02/02/1902. Is anyone else aware of this and do we know if Oracle are aware of it? It's a bit of a pain and will require a custom script to resolve, which needlessly complicates any report involving a Date of Birth.

Howdy, Stranger!

Log In

To view full details, sign in.


Don't have an account? Click here to get started!