I have a query where I have to run monthly. My objective is to be able to run the code in any day of the month, and it will run the code for the prior month. So for example, if I run the query today, feb 22, it'll run the code for January
My column that stores my 'Date' is actually just a number data type, and it is in the format of YYYYMM, for example, 201602. How do I use the SYSDATE to output YYYYMM format?
I tried:
SELECT
EXTRACT(YEAR FROM (LAST_DAY(ADD_MONTHS(TO_CHAR(SYSDATE,'DD-MON-YY'),-1)))) ||(EXTRACT(MONTH FROM (LAST_DAY(ADD_MONTHS(TO_CHAR(SYSDATE,'DD-MON-YY'),-1))))) AS Reporting_Month
FROM DUAL;
but it only gives me 20171I tried to add a case statement, but that did not work either. any ideas?
SELECT
EXTRACT(YEAR FROM (LAST_DAY(ADD_MONTHS(TO_CHAR(SYSDATE,'DD-MON-YY'),-1))))
|| CASE
WHEN (EXTRACT(MONTH FROM (LAST_DAY(ADD_MONTHS(TO_CHAR(SYSDATE,'DD-MON-YY'),-1))))) IN ('10','11','12')
THEN (EXTRACT(MONTH FROM (LAST_DAY(ADD_MONTHS(TO_CHAR(SYSDATE,'DD-MON-YY'),-1)))))
ELSE 0|| EXTRACT(MONTH FROM (LAST_DAY(ADD_MONTHS(TO_CHAR(SYSDATE,'DD-MON-YY'),-1)))) END AS Current_Reporting_Month
FROM DUAL;