Home » Other » Training & Certification » What is Problem with DAte Query
What is Problem with DAte Query [message #303604] Sat, 01 March 2008 01:21 Go to next message
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
Re: What is Problem with DAte Query [message #303606 is a reply to message #303604] Sat, 01 March 2008 01:28 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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]

Report message to a moderator

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


Current Time: Fri Dec 09 23:23:18 CST 2016

Total time taken to generate the page: 0.09045 seconds