Skip to Main Content

ORA-01843 not a valid month when trying to use Case Statements

User_MXQR1Dec 2 2022

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.

Comments
Post Details
Added on Dec 2 2022
9 comments
100 views