| How Oracle Handles the date [message #49634] |
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 #49639 is a reply to message #49634] |
Fri, 01 February 2002 02:03  |
Manu Gupta
Messages: 44
Registered: January 2002
|
Member |
|
|
Dear Milind S Deobhankar,
i don't now why Oracle is behaving like this. I followed the same steps as described by you and the result is as under -
01-01-2001
01-01-2066
01-01-2000
01-01-2003
01-01-2005
01-01-2020
01-01-2050
01-01-2048
01-01-2035
In case of 1966 it is 2066 and same with 1950.
|
|
|
|
| Re: How Oracle Handles the date [message #49645 is a reply to message #49634] |
Fri, 01 February 2002 04:03 |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
I looked at the Oracle doc and it explains well. I have included a part of it here. Do a search on 'RR Date Format'
The less than 50 and more than 50 theory applies here.
From Oracle Doc
==
RR Date Format Examples
Assume these queries are issued between 1950 and 1999:
SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year"
FROM DUAL;
Year
----
1998
SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"
FROM DUAL;
Year
----
2017
Now assume these queries are issued between 2000 and 2049:
SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year"
FROM DUAL;
Year
----
1998
SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"
FROM DUAL;
Year
----
2017
Note that the queries return the same values regardless of whether they are issued before or after the year 2000. The RR date format element allows you to write SQL statements that will return the same values from years whose first two digits are different.
|
|
|
|
| Re: How Oracle Handles the date [message #51081 is a reply to message #49634] |
Wed, 24 April 2002 00:25 |
JAIDEEP MUHURI
Messages: 1
Registered: April 2002
|
Junior Member |
|
|
i am coding in php4.1.i have an apache server running on my machine along with the php engine.Oracle 8i client has also been installed.
interface has to be made with oracle 8i database on a sun solaris server.everything works well except this query:
$res=odbc_exec($res,"SELECT TO_CHAR(TO_DATE(COLUMN WITH DATE,'DD-MM-YY'),'DD-MM-YY') "AP" FROM TABLENAME");
any answers.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
