srikotesh
asked on
how to get the substring from the column value
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
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
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-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....
Apologies....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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