| sql [message #372551] |
Wed, 21 February 2001 22:07  |
Jey
Messages: 4
Registered: February 2001
|
Junior Member |
|
|
hi,
when i query like,
select TO_CHAR(add_months(to_date('12/01/01', 'DD/MM/YYYY') ,3),'YYYYMM') from dual
i am getting '000104'
but when i query like,
select TO_CHAR(add_months(to_date('12/01/2001', 'DD/MM/YYYY') ,3),'YYYYMM') from dual
i am getting '200104'. but this one is correct. but i just wondering what's wrong in the year?
Thanks is advance
jey
|
|
|
|
| Re: sql [message #372553 is a reply to message #372551] |
Thu, 22 February 2001 02:58  |
J.S HAN
Messages: 12
Registered: November 2000
|
Junior Member |
|
|
Hi,
Check below sql.
1* select to_char(to_date('12/01/01', 'DD/MM/YYYY'),'YYYY MM DD') from dual
SQL> /
TO_CHAR(TO
----------
0001 01 12
this means
year : AC 1
month : Jan
day : 12
1* select to_char(to_date('12/01/2001', 'DD/MM/YYYY'),'YYYY MM DD') from dual
SQL> /
TO_CHAR(TO
----------
2001 01 12
OR
1* select to_char(to_date('12/01/01', 'DD/MM/YY'),'YYYY MM DD') from dual
SQL> /
TO_CHAR(TO
----------
2001 01 12
it's collect.
|
|
|
|
| Re: sql [message #372562 is a reply to message #372551] |
Thu, 22 February 2001 07:23 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
to_date('12/01/01', 'DD/MM/YYYY')
returns 12-Jan-0001 because you have specified that you are going to give Oracle a 4 char year format. Oracle will lpad the year with 0 to make up the 4 char year when you do not provide all 4 chars. The following 4 formats will all return year 1:
to_date('12/01/1', 'DD/MM/YYYY')
to_date('12/01/01', 'DD/MM/YYYY')
to_date('12/01/001', 'DD/MM/YYYY')
to_date('12/01/0001', 'DD/MM/YYYY')
to_date('12/01/2001', 'DD/MM/YYYY')
returns 12-Jan-2001 because you have specified all 4 chars.
to_date('12/01/01', 'DD/MM/YY')
returns 12-Jan-2001 because you have specified that you are only going to give a 2 char year format and Oracle will assume you want the current century.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
