how to get the substring from the column value

srikotesh
srikotesh used Ask the Experts™
on
HI Experts,

how to get the substring from the below query

SELECT ADD_MONTHS(TRUNC (LAST_DAY (SYSDATE)), -2)+1 start_date FROM DUAL
//01-AUG-18
expected o/p: AUG-18
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi srikotesh,

In Oracle, the easiest way to format a date string is with the TO_CHAR function.

SELECT TO_CHAR(ADD_MONTHS(TRUNC (LAST_DAY (SYSDATE)), -2)+1, 'MMM-DD') start_date FROM DUAL
I guess you need year:
SELECT to_char(ADD_MONTHS(TRUNC (LAST_DAY (SYSDATE)), -2)+1, 'MON-YY') start_date FROM DUAL

not day
SELECT TO_CHAR(ADD_MONTHS(TRUNC (LAST_DAY (SYSDATE)), -2)+1, 'MON-DD') start_date FROM DUAL

Apologies....
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

awking00Information Technology Specialist
Commented:
select to_char(add_months(trunc(sysdate,'mm'),-1),'MON-RR') from dual;

Author

Commented:
how to give alias name to this query


SELECT to_char(ADD_MONTHS(TRUNC (LAST_DAY (SYSDATE)), -2)+1, 'MON-YY') start_date FROM DUAL

SELECT to_char(ADD_MONTHS(TRUNC (LAST_DAY (SYSDATE)), -2)+1, 'MON-YY') start_date FROM DUAL  as year
select 'abc' as astring,
(SELECT to_char(ADD_MONTHS(TRUNC (LAST_DAY (SYSDATE)), -2)+1, 'MON-YY') start_date FROM DUAL) as year from dual

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial