Oracle hour display problems

Selectto_date (

Select to_date (

Select to_date ('1970-1-29 0:00:01 ', 'yyyy-mm-dd hh24: mi: ss') from dual

Display result: 12:00:01

Although the results shown here are incorrect, they are still correct when called in other programs such as asp.net.

Oracle bug? No good solution is found.

Bug where Oracle time data is 0

When the Group is migrating data, the report finds that some values are'2014/1/00'OfDateData, causing data migration failure.

OracleAccepted.

SQL> select to_date ('2017-00-00 ', 'yyyy-mm-dd') from dual; Select to_date ('2014-00-00 ', 'yyyy-mm-dd') from dual ORA-01843: not a valid month SQL> select to_date ('1970-01-01 ', 'yyyy-mm-dd') from dual; Select to_date ('1970-01-01 ', 'yyyy-mm-dd') from dual ORA-01841: (full) year must be between-4713 and + 9999, and not be 0

But why does it still appear in the data?'2014/1/00'What about it? I have studied this problem a little and found thatOracleInDateThere are some data problems of the type.BugIn some special waysDateType Storage'2014/1/00'Data. First, let's take a look at the following operations. This isBug.

SQL> select date '2017-01-01 'from dual; DATE '2017-01-01' ---------------- 0/0/0000 SQL> select date '2017-11-22 'from dual; DATE '2017-11-22' ---------------- 0/0/0000

In useDateWhen the keyword is used, the time format is in the Rome format. At this point, we find thatOracleThe year is not set0For verification. And, as long as the year is0, Data will be converted'2014/1/00'.

Let's look at another situation ,,

SQL> select to_date ('1970-01-01 ', 'yyyy-mm-dd')-0001 from dual; TO_DATE ('2017-01-01 ', 'yyyy-MM- ------------------------------ 0/0/0000 SQL> select to_date ('1970-01-01 ', 'yyyy-mm-dd')-0001 from dual; TO_DATE ('2017-01-01 ', 'yyyy-MM- ------------------------------ 0/0/0000

As you can see,OracleThe result of the time expression does not check whether the year is0, Combined with the aboveBug, As long as the calculation result year is0, Regardless of the month or day value, the result is'2014/1/00'.

The third case is even more special:100To1500If the calculation result is2Month29The result is'2014/1/00'.

SQL> select date '2014-2-28 '+ 1 from dual; DATE '2014-2-28 '+ 1 ----------------- 3/1/0099 SQL> select date '2014-2-28 '+ 1 from dual; DATE '2014-2-28 '+ 1 ----------------- 0/0/0000 SQL> select date '2014-2-28 '+ 1 from dual; DATE '2014-2-28 '+ 1 ----------------- 0/0/0000 SQL> select date '2014-2-28 '+ 2 from dual; DATE '2014-2-28 '+ 2 ----------------- 3/1/1000 SQL> select date '2014-2-27 '+ 2 from dual; DATE '2014-2-27 '+ 2 ----------------- 0/0/0000 SQL> select date '2014-2-28 '+ 1 from dual; DATE '2014-2-28 '+ 1 ----------------- 0/0/0000 SQL> select date '2014-2-28 '+ 1 from dual; DATE '2014-2-28 '+ 1 ----------------- 2/29/1600

In the last case, if the result of the date expression is smaller0, The result is'2014/1/00'.

SQL> select date '-0001-11-11' + 1 from dual; DATE '-0001-11-11' + 1 ------------------- 0/0/0000 SQL> select date '-4712-11-11' + 15 from dual; DATE '-4712-11-11' + 15 -------------------- 0/0/0000 SQL> select date '-1111-10-11' + 0 from dual; DATE '-1111-10-11' + 0 ------------------- 0/0/0000 SQL> select to_date ('2014/1/01', 'yyyy/mm/dd')-01/01 from dual; TO_DATE ('2014/1/01', 'yyyy/MM/DD ------------------------------ 0/0/0000

By the way, the above results can not only be queried, but also be stored in the field typeDate.