Home » SQL & PL/SQL » SQL & PL/SQL » How oracle handles the date
How oracle handles the date [message #18469] Thu, 31 January 2002 23:02 Go to next message
Milind S Deobhankar
Messages: 33
Registered: January 2002
Member
Hi one thing confusing me.
Please see the code carefully:

SQL> select sysdate from dual
/
SYSDATE WAS 01-01-2001

SQL>create table datetest
(dob date)
/
Table created.

SQL>insert into datetest values('1-jan-01');
1 row created.

10:46:30 SQL> select to_char(dob,'dd-mm-yyyy') from datetest;
TO_CHAR(DO
----------
01-01-2001

10:47:04 SQL> insert into datetest values('1-jan-66');
1 row created.

10:47:21 SQL> select to_char(dob,'dd-mm-yyyy') from datetest;
TO_CHAR(DO
----------
01-01-2001
01-01-1966

10:47:25 SQL> insert into datetest values('1-jan-00');
1 row created.

10:48:39 SQL> select to_char(dob,'dd-mm-yyyy') from datetest;
TO_CHAR(DO
----------
01-01-2001
01-01-1966
01-01-2000

10:48:42 SQL> insert into datetest values('1-jan-03');
1 row created.

10:49:04 SQL> select to_char(dob,'dd-mm-yyyy') from datetest;
TO_CHAR(DO
----------
01-01-2001
01-01-1966
01-01-2000
01-01-2003

10:49:08 SQL> insert into datetest values('1-jan-05');
1 row created.

10:49:49 SQL> select to_char(dob,'dd-mm-yyyy') from datetest;
TO_CHAR(DO
----------
01-01-2001
01-01-1966
01-01-2000
01-01-2003
01-01-2005

10:49:51 SQL> insert into datetest values('1-jan-20');
1 row created.

10:50:06 SQL> select to_char(dob,'dd-mm-yyyy') from datetest;
TO_CHAR(DO
----------
01-01-2001
01-01-1966
01-01-2000
01-01-2003
01-01-2005
01-01-2020
6 rows selected.

10:50:13 SQL> insert into datetest values('1-jan-50');
1 row created.

10:50:26 SQL> select to_char(dob,'dd-mm-yyyy') from datetest;
TO_CHAR(DO
----------
01-01-2001
01-01-1966
01-01-2000
01-01-2003
01-01-2005
01-01-2020
01-01-1950

7 rows selected.

10:50:29 SQL> insert into datetest values('1-jan-48');
1 row created.

10:50:48 SQL> insert into datetest values('1-jan-35');
1 row created.

10:51:01 SQL> select to_char(dob,'dd-mm-yyyy') from datetest;
TO_CHAR(DO
----------
01-01-2001
01-01-1966
01-01-2000
01-01-2003
01-01-2005
01-01-2020
01-01-1950
01-01-2048
01-01-2035

9 rows selected.

By mistake the client side programmer are inserting the date in format dd-mon-yy. But one very amusing things happens.

Firstly when i insert the date 01-jan-01 then the year insert was 2001 when retriev in format dd-mon-yyyy but when the date was 1-jan-66 then the year was 1966.

So agin i tried to insert the date like this 1-jan-50 then the year was 1950 but when i inserted the date 1-jan-48 then the year was 2048 and not 1948 so can anybody help me in this matter the sysdate is 01-01-2001.

How does the oracle handles the date internally can we chage the same or not. I suppose oracle is taking +50 to -50 range from the current date insertion.

Any help will be appreciated
Re: How oracle handles the date [message #18470 is a reply to message #18469] Thu, 31 January 2002 23:59 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
The result depends which format you are using.

If you are using the RR format for the year (which is, I guess, your case) you have the following role:
- If the year is <50 and the last 2 digits of the current year are >=50, the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
- If the year is >=50 and the last 2 digits of the current year are <50, the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

If you are using the YY format it will asume that the first 2 digits are the same as the one of the current year.

For more detail see:
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/sql_elem.htm#34926
Previous Topic: How to
Next Topic: Difficulty Framing a Query
Goto Forum:
  


Current Time: Thu Jul 02 10:21:07 CDT 2020