| Date Problem A Serious One [message #18516] |
Sun, 03 February 2002 22:51  |
Milind S Deobhankar
Messages: 33
Registered: January 2002
|
Member |
|
|
Below is the abstract of the LST which was generated on Oracle 8.1.7 .
SQL> create table testdate
2 (testing date
3 )
4 /
Table created.
SQL> insert into testdate
2 values('01-01-02')
3 /
values('01-01-02')
*
ERROR at line 2:
ORA-01843: not a valid month
SQL> insert into testdate
2 values('01-jan-02')
3 /
1 row created.
SQL> insert into testdate values('&enterdate')
2 /
Enter value for enterdate: 01-jan-48
old 1: insert into testdate values('&enterdate')
new 1: insert into testdate values('01-jan-48')
1 row created.
SQL> /
Enter value for enterdate: 01-jan-55
old 1: insert into testdate values('&enterdate')
new 1: insert into testdate values('01-jan-55')
1 row created.
SQL> /
Enter value for enterdate: 01-dec-50
old 1: insert into testdate values('&enterdate')
new 1: insert into testdate values('01-dec-50')
1 row created.
SQL> /
Enter value for enterdate: 01-dec-99
old 1: insert into testdate values('&enterdate')
new 1: insert into testdate values('01-dec-99')
1 row created.
SQL> /
Enter value for enterdate: 01-dec-39
old 1: insert into testdate values('&enterdate')
new 1: insert into testdate values('01-dec-39')
1 row created.
SQL> select * from testdate
2 /
TESTING
---------
01-JAN-02
01-JAN-48
01-JAN-55
01-DEC-50
01-DEC-99
01-DEC-39
6 rows selected.
SQL> select to_char(testing,'dd-mon-yyyy') from testdate;
TO_CHAR(TES
-----------
01-jan-2002
01-jan-2048
01-jan-1955
01-dec-1950
01-dec-1999
01-dec-2039
6 rows selected.
SQL> select to_char(testing,'dd-mon-rr') from testdate;
TO_CHAR(T
---------
01-jan-02
01-jan-48
01-jan-55
01-dec-50
01-dec-99
01-dec-39
6 rows selected.
SQL> select to_char(testing,'dd-mm-yyyy') from testdate;
TO_CHAR(TE
----------
01-01-2002
01-01-2048
01-01-1955
01-12-1950
01-12-1999
01-12-2039
6 rows selected.
SQL> spool off;
The problem is that when i insert the date ('01-jan-50') then the date get inserted in yyyy format like 1950 but when I insert the date '01-jan-1948' it get inserted as 2048.
I am not getting why this is happens. Problem is that our client side is firing the query similar to the above lst file .
So if anybody can help me how to prevent then it will be a great help to me.
Thanks in advance.
|
|
|
|
| Re: Date Problem A Serious One [message #18517 is a reply to message #18516] |
Sun, 03 February 2002 23:18  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
The problem is that your default date format uses the RR format for the year. Use the to_date function to format your date and YY or YYYY for the year format.
For example:
to_date('01.01.50','DD.MM.YY')
to_date('01.01.2050','DD.MM.YYYY')
|
|
|
|
| Re: Date Problem A Serious One [message #18525 is a reply to message #18516] |
Mon, 04 February 2002 08:04 |
INTROV
Messages: 20
Registered: February 2002
|
Junior Member |
|
|
RR Yes Given a year with 2 digits:
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 two 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 the same as the first 2 digits of the current year.
RRRR Yes Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don’t want this functionality, enter the 4-digit year.
Hence use to_date function using the appropriate format while inserting the date.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
