| How oracle handles the date [message #18469] |
Thu, 31 January 2002 23:02  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
