I am trying to replace part of a day with a date value. eg,
select TRUNC(SYSDATE) from dual;
Result: 28/11/2014
I wanted to replace only 28 with another value of the number (X). So I can get a result like X / 11/2014.
Can you help me?
Thanks Advance,
Murugan.
trunc(sysdate,'MM') + (x-1)
would do it. trunc(sysdate,'MM') returns the first month. Then you add, however, how many days you want the date you want.
trunc(sysdate,'MM')
If it is a character, you can use the following query:
SELECT 'X'||SUBSTR(TRUNC(SYSDATE),3) FROM DUAL;
If you want to replace with a different number, then:
SELECT TO_DATE(21||SUBSTR(TRUNC(SYSDATE),3),'DD-MON-YY') FROM DUAL;