OraFAQ Forum: Training & Certification » What is Problem with DAte Query

Home » Other » Training & Certification » What is Problem with DAte Query

Sat, 01 March 2008 01:21

balajiags
Messages: 10
Registered: July 2007

Junior Member

select TO_DATE(TO_CHAR(SYSDATE, 'DDMM')||TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))-5, 'DDMMYYYY') from dual

Sat, 01 March 2008 01:28

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-01843: not a valid month

Mixing strings and numbers is a bad idea, you don't know how conversions are made.
If you remove the final TO_DATE you see the problem:

SQL> select TO_CHAR(SYSDATE, 'DDMM')||TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))-5 from dual;
1032003

In addition, if you want to substract 5 years, have a look at ADD_MONTHS function.

Regards
Michel

[Updated on: Sat, 01 March 2008 01:32]

Previous Topic:	Defintion,use,restriction with execute immediate?
Next Topic:	I need help on pinned, clean, dirty and free buffers

Goto Forum:

]

Current Time: Thu Nov 21 13:25:57 CST 2024