ORA-01843 not a valid month.
A little bit of of what is going on.
I am a business analyst and I am trying to build a report that tells me when an order is due.
I have read only access and cannot create tables.
So I created a "read only "CTE" table" that works great for information.
I can join this to anything with Week numbers or Days of the week depending on my needs.
As this is a business day week schedule I made it so it skips Saturday and Sunday to go the the correct
corresponding Date.
The information is perfect BUT the problem is that it is purely informational
I cannot use those dates to create "DUE_DATES" based on an "ADDDATE" that is in an existing (non-dual) table.
SELECT DISTINCT
TO_CHAR(DATET,'D') WEEK_NUMBER,
CASE WHEN TO_CHAR(DATET-9,'D') = 1 THEN TO_CHAR((DATET-11)+(3/24),'MM/DD/YYYY HH24:MI')
WHEN TO_CHAR(DATET-9,'D') = 7 THEN TO_CHAR((DATET-11)+(3/24),'MM/DD/YYYY HH24:MI')
ELSE TO_CHAR((DATET-9)+(3/24),'MM/DD/YYYY HH24:MI') END PAST_CUT_OFF,
CASE WHEN TO_CHAR(DATET-2,'D') = 1 THEN TO_CHAR((DATET-4)+(3/24),'MM/DD/YYYY HH24:MI')
WHEN TO_CHAR(DATET-2,'D') = 7' THEN TO_CHAR((DATET-4)+(3/24),'MM/DD/YYYY HH24:MI')
ELSE TO_CHAR((DATET-2)+(3/24),'MM/DD/YYYY HH24:MI') END CUT_OFF,
CASE WHEN TO_CHAR(DATET+5,'D') = 1 THEN TO_CHAR((DATET+3)+(3/24),'MM/DD/YYYY HH24:MI')
WHEN TO_CHAR(DATET+5,'D') = 7 THEN TO_CHAR((DATET+3)+(3/24),'MM/DD/YYYY HH24:MI')
ELSE TO_CHAR((DATET+5)+(3/24),'MM/DD/YYYY HH24:MI') END NEXT_CUT_OFF,
DATET-14 LATE_DATE2,
DATET-7 LATE_DATE,
DATET DUE_DATE,
DATET+7 DUE_DATE2,
DATET+14 DUE_DATE3,
TO_CHAR(DATET,'DAY') DAY_OF_WEEK
FROM
(SELECT
CASE WHEN TO_CHAR(TRUNC(CURRENT_DATE) + LEVEL-1,'D') = 1 THEN TRUNC(CURRENT_DATE) + LEVEL+1
WHEN TO_CHAR(TRUNC(CURRENT_DATE) + LEVEL-1,'D') = 7 THEN TRUNC(CURRENT_DATE) + LEVEL
ELSE TRUNC(CURRENT_DATE) + LEVEL-1 END DATET
FROM DUAL
CONNECT BY LEVEL <=7)
ORDER BY DATET;
For example lets say I have a table called "ORDERS" and the order was added to the system on 11/23/2022
Orders can come in at anytime during the week, but Stores have a designated ship day (Mon-Fri).
IF the order comes in after a specific CUT OFF (found in the "Table" I created) then the order gets pushed to the
following week. I can use many CASE statements to find out when the order is actually Due.
CASE when ADDDATE > CUT_OFF_DATE Then DUE_DATE ELSE NEXT_CUT_OFF END AS DUE_DATE (for simplicity)
I would have many more case statements
But there is an error when I run this ORA-01843 not a valid month.
i try and format the dates with masks TO_DATE(x , 'MM/DD/YYYY HH24:MI') x being the variable.
and still the same error.
I'm at a loss.
I would like not to have to export 10s of thousands of lines to do this in excel, this should be able to be done in report.