Forum Stats

  • 3,839,340 Users
  • 2,262,485 Discussions
  • 7,900,937 Comments

Discussions

Oracle DB date conversion outputs 2000s instead of 1990

Hubertus
Hubertus Member Posts: 1 Green Ribbon

Problem:

All year 1999 become 2000 when I fetch table data with SQL query from a third party web app (the web app has a SQL query box)

What we have tried:

  1. Query the data directly from Oracle DB Management tool yet the year outputs 19** which is totally fine.
  2. https://stackoverflow.com/questions/33562929/oracle-date-conversion-outputs-2000s-instead-of-required-1990s. Tried different variety of queries based on this discussion on stackoverflow.

Any idea what might be the root of the issue? Cheers!

Answers

  • Jim Smith
    Jim Smith Member Posts: 2,877 Silver Trophy

    If oracle tools return the correct result then the problem almost certainly lies with the third party web app.

    If you query using to_char(datecolumn,'dd-mm-yyyy') this will show what year is actually stored.

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    And to follow on from comment by @Jim Smith , I'd say it almost certain that your web app is using either a to_char mask or an NLS_DATE_FORMAT setting that uses the 'RR' construct instead of YYYY. The RR (or RRRR) mask was meant as a temporary solution to buy time in remediating Y2k. That was 23 years ago, and the nature of what RR does is now showing increasing reports of 'Y2k, The Sequel'.