This discussion is archived
14 Replies Latest reply: Jan 10, 2013 7:22 AM by Mark Malakanov (user11181920) RSS

How to check if a date is a valid date in Oracle?

983611 Newbie
Currently Being Moderated
Hello,
I have a date '0212-09-17', and it seems Oracle did accept it when I stored it in a table (column type = DATE).
However, when it was flowing downstream into other servers such as SQL server, it caused issues, because SQL treats it as an invalid date.
Then I added in a check to check if dates like the above is valid before flowing downstream.
But surprisingly if i use TO_DATE('0212-09-17, 'YYYY-MM-DD') it seems to return a valid date... but the year doesn't look right.
I did a bit of research on google, didn't find good answers on this. Can you tell me what would be the best way to check for
valid dates in Oracle?


Much appreciated,
  • 1. Re: How to check if a date is a valid date in Oracle?
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    Hi,

    Welcome to the forums. Generally it's a good idea to post your 4-digit Oracle version and o/s information.

    Have a look at the Oracle Built-in Data Types in the documentation.

    You will find there that the "Valid date range from January 1, 4712 BC, to December 31, 9999 AD." in Oracle.

    You can see your date is a valid date within that range:
    SQL> select TO_DATE('0212-09-17', 'YYYY-MM-DD') as sample_date from dual;
    
    SAMPLE_DATE
    --------------------
    17-SEP-0212 00:00:00
    
    1 row selected.
    As to whether that is a valid date in another system is up to that system.

    Regards,

    Mark
  • 2. Re: How to check if a date is a valid date in Oracle?
    rp0428 Guru
    Currently Being Moderated
    >
    I have a date '0212-09-17', and it seems Oracle did accept it when I stored it in a table (column type = DATE).
    However, when it was flowing downstream into other servers such as SQL server, it caused issues, because SQL treats it as an invalid date.
    Then I added in a check to check if dates like the above is valid before flowing downstream.
    But surprisingly if i use TO_DATE('0212-09-17, 'YYYY-MM-DD') it seems to return a valid date... but the year doesn't look right.
    I did a bit of research on google, didn't find good answers on this. Can you tell me what would be the best way to check for
    valid dates in Oracle?
    >
    That is in the valid date range for Oracle assuming the format is YYYY-MM-DD. If you can store it in an Oracle DATA column or do a TO_DATE on it then it is a valid date for Oracle.

    That is NOT a valid date for SQL Server. Dates prior to the year 1753 are not valid in SQL Server.
    See the Microsoft docs
    http://msdn.microsoft.com/en-us/library/ms187819.aspx
    >
    Date range
    January 1, 1753, through December 31, 9999
  • 3. Re: How to check if a date is a valid date in Oracle?
    983611 Newbie
    Currently Being Moderated
    thank you Mark!

    So looks like it is a valid date in Oracle. We're using Oracle 11.2 and Windos 7 Operating system.


    But it's interesting to know the date range that Oracle considers to be valid dates.

    Thanks you for your help
  • 4. Re: How to check if a date is a valid date in Oracle?
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    "Valid date range from January 1, 4712 BC, to December 31, 9999 AD."
    I am puzzled why the date range is that narrow, from my opinion when Oracle has NUMBER range that you can count every planet in Universe, there should be DATE type with a range at least as 32B years (16000000000 BC to 16000000000 AD) to operate with astronomical ages as well.
    (Another question is - why to have such a huge range (precision) for NUMBER?)
  • 5. Re: How to check if a date is a valid date in Oracle?
    sb92075 Guru
    Currently Being Moderated
    Mark Malakanov (user11181920) wrote:
    "Valid date range from January 1, 4712 BC, to December 31, 9999 AD."
    I am puzzled why the date range is that narrow, from my opinion when Oracle has NUMBER range that you can count every planet in Universe, there should be DATE type with a range at least as 32B years (16000000000 BC to 16000000000 AD) to operate with astronomical ages as well.
    Send your concerns to a SQL Standards Committee
  • 6. Re: How to check if a date is a valid date in Oracle?
    jgarry Guru
    Currently Being Moderated
    Check out this, including the enlightening doc cited: http://rwijk.blogspot.com/2008/10/year-zero.html

    You never know when your banking software might need some [url http://en.wikipedia.org/wiki/Hungarian_peng%C5%91_hyperinflation#Hyperinflation]precision.
  • 7. Re: How to check if a date is a valid date in Oracle?
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    Send your concerns to a SQL Standards Committee
    though ANSI SQL (2011) specifies year range 1 to 9999 for DATE, it does not specify range for NUMBER.

    http://www.jtc1sc32.org/doc/N2151-2200/32N2153T-text_for_ballot-FDIS_9075-1.pdf
  • 8. Re: How to check if a date is a valid date in Oracle?
    rp0428 Guru
    Currently Being Moderated
    >
    though ANSI SQL (2011) specifies year range 1 to 9999 for DATE
    >
    Then what is the basis for this statement?
    >
    I am puzzled why the date range is that narrow
  • 9. Re: How to check if a date is a valid date in Oracle?
    jgarry Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    though ANSI SQL (2011) specifies year range 1 to 9999 for DATE
    >
    Then what is the basis for this statement?
    >
    I am puzzled why the date range is that narrow
    >
    I suppose because Oracle already goes beyond the standard?
  • 10. Re: How to check if a date is a valid date in Oracle?
    rp0428 Guru
    Currently Being Moderated
    Reread it - the comment about the range being too narrow was about this from SB
    >
    Valid date range from January 1, 4712 BC, to December 31, 9999 AD."
    {quote}
  • 11. Re: How to check if a date is a valid date in Oracle?
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    Mark Malakanov (user11181920) wrote:
    "Valid date range from January 1, 4712 BC, to December 31, 9999 AD."
    I am puzzled why the date range is that narrow, from my opinion when Oracle has NUMBER range that you can count every planet in Universe, there should be DATE type with a range at least as 32B years (16000000000 BC to 16000000000 AD) to operate with astronomical ages as well.
    (Another question is - why to have such a huge range (precision) for NUMBER?)
    Hmm, well, I've never looked at the date range and thought, "wow, that's too small", and whilst I am not a calendar expert, I suspect the range is sufficient to cover the calendar systems supported by Oracle Database (with January 1, 4712 BC being Julian Day 1).

    The calendars supported in current releases (with Gregorian being implicit) are here:

    http://docs.oracle.com/cd/E11882_01/server.112/e10729/applocaledata.htm#NLSPG594

    Is there a commonly used calendar system that has dates outside this range (January 1, 4712 BC, to December 31, 9999 AD.)?

    As to the precision question, maybe the precision is as large as it is to deal with the astronomical ages you mention or any other suitably large number. I'm sure there was a design decision made however many years ago it is now and that decision took whatever factors that were deemed relevant into account. I'm not aware of any document which discusses this though.

    As ever... I'm always open to addition/correction/comments/etc
  • 12. Re: How to check if a date is a valid date in Oracle?
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    I am not argue about practicality and reasonability of existing date range.
    Most likely it covers all calendars on Earth including Mayan.
    It is just out of curiosity. For example with Julian day 1. Why it is January 1, 4712 BC? Julian time in Oracle is not a Julian Calendar. Julian representation of time is astronomical and is used for measuring periods. Julian year is 365.25 Julian days, and Julian day is 24*60*60 seconds. Where that January 1, 4712 BC came from?
    Why it is not Jan 1-st, 1 AD?
    It is, as said, astronomical representation of time. So, I assume, Oracle guys were thinking about Oracle for astronomical calculations. But astronomers sometimes operate values larger than 4712 years back.
    So why not to make DATE internally as a NUMBER(*,5), 5 is for seconds?
    It could give range for years with 30 digits! Or NUMBER with scale 14 for nanoseconds (FF9)? Or a flexible NUMBER where user could define what it needs either more years or more granular sub-second fraction?
    Like DATE(4,5) or DATE(*,20).
    Then just represent this number as a date (and time) in this or that calendar. Something like that. Why it was needed to invent special internal representation for DATE?

    Also, again, ANSI says - year range must cover 1 to 9999 years. Assuming BC era too. So here Oracle does not comply. You cannot enter Jan 1, 9999 BC.
    As to the precision question, maybe the precision is as large as it is to deal with the astronomical ages you mention or any other suitably large number. I'm sure there was a design decision made however many years ago it is now and that decision took whatever factors that were deemed relevant into account.
    It would be interesting to know. Why they decided to go so crazy making NUMBER with 38 digit precision 30 years ago, while being quite modest with DATE? ;)
  • 13. Re: How to check if a date is a valid date in Oracle?
    rp0428 Guru
    Currently Being Moderated
    >
    For example with Julian day 1. Why it is January 1, 4712 BC? Julian time in Oracle is not a Julian Calendar. Julian representation of time is astronomical and is used for measuring periods. Julian year is 365.25 Julian days, and Julian day is 24*60*60 seconds. Where that January 1, 4712 BC came from?
    Why it is not Jan 1-st, 1 AD?
    >
    The Oracle docs tell you where the 4712 BC came from. See the Datatypes section of the SQL Language doc
    http://docs.oracle.com/cd/B14117_01/server.101/b10759/sql_elements001.htm
    >
    Using Julian Days
    A Julian day number is the number of days since January 1, 4712 BC. Julian days allow continuous dating from a common reference. You can use the date format model "J" with date functions TO_DATE and TO_CHAR to convert between Oracle DATE values and their Julian equivalents.

    Note:

    Oracle Database uses the astronomical system of calculating Julian days, in which the year 4713 BC is specified as -4712. The historical system of calculating Julian days, in contrast, specifies 4713 BC as -4713. If you are comparing Oracle Julian days with values calculated using the historical system, then take care to allow for the 365-day difference in BC dates. For more information, see http://aa.usno.navy.mil/faq/docs/millennium.html.
    >
    See also the wiki http://en.wikipedia.org/wiki/Julian_day
    >
    Julian day refers to a continuous count of days since the beginning of the Julian Period used primarily by astronomers.

    The Julian Day Number (JDN) is the integer assigned to a whole solar day in the Julian day count starting from noon Greenwich Mean Time, with Julian day number 0 assigned to the day starting at noon on January 1, 4713 BC proleptic Julian calendar. (November 24, 4714 BC in the proleptic Gregorian calendar.) The Julian day number for today, 8 January 2013, is 2456301.

    The Julian Date (JD) of any instant is the Julian day number for the preceding noon plus the fraction of the day since that instant. Julian Dates are expressed as a Julian day number with a decimal fraction added.[1] The current Julian Date is 2456301.3405208.
    >
    And in this you are wrong.
    >
    Also, again, ANSI says - year range must cover 1 to 9999 years. Assuming BC era too. So here Oracle does not comply. You cannot enter Jan 1, 9999 BC.
    >
    Oracle is in compliance. An Oracle date covers 1 to 9999 years and also covers an addition 4712 years BC.

    You are the one 'assuming' BC era too. The ANSI doc doesn't mention it. Year range 1 to 9999 clearly can only mean AD. Otherwise what would 'year 1' represent?

    You haven't suggested at all another standard that Oracle might have used.
  • 14. Re: How to check if a date is a valid date in Oracle?
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    thanks for pointing me to 4712 BC origin.
    you are right, ANSI does not specify BC era.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points