SQL Error, Code=1843, Message=ORA-01843: not a valid month (50,380). Matching format exactly using TO_CHAR(TO_DATE()) function

Question

Goal is to extract YYYY from A.SCC_ROW_ADD_DTTM with format of DateTm

Using peoplesoft Query Manager, adding an expression hoping to pull Year from a DateTm field.

Running a query on the DateTm field displays date in the following format: 03/19/2017 12:00:23PM

And so I tried

TO_CHAR(TO_DATE(A.SCC_ROW_ADD_DTTM, 'MM/DD/YYYY HH:MI:SSPM'), 'YYYY')

But keep getting the error not a valid month (50,380), but every date in the list starts with months in the format of XX (01-12). Full code below

SELECT A.ITEM_TYPE, B.DESCR, SUM( A.ITEM_AMT- A.APPLIED_AMT), TO_CHAR(TO_DATE(TO_CHAR(CAST((A.SCC_ROW_ADD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 'MM/DD/YYYY HH:MI:SSPM'), 'YYYY'), TO_CHAR(CAST((A.SCC_ROW_ADD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')
  FROM PS_ITEM_SF A, PS_ITEM_TYPE_TBL B
  WHERE ( B.ITEM_TYPE = A.ITEM_TYPE
    AND ( A.ITEM_TYPE IN ('600000050010','600000050020','600000050030')
     AND B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.ITEM_TYPE = B_ED.ITEM_TYPE
          AND B_ED.EFFDT <= SYSDATE) ))
  GROUP BY  A.ITEM_TYPE,  B.DESCR,  TO_CHAR(TO_DATE( TO_CHAR(CAST((A.SCC_ROW_ADD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 'MM/DD/YYYY HH:MI:SSPM'), 'YYYY'),  A.SCC_ROW_ADD_DTTM
  HAVING ( SUM( A.ITEM_AMT- A.APPLIED_AMT) > 0)
  ORDER BY 1

I've tried changing the date format but get worse errors when changing from anything but what the expression is now, I just have no idea why it says the months are incorrect.

EDIT: I now notice that peoplesoft Query Manager automatically casts the field to a timestamp, there is no way to prevent this, and so I think there is no way to fix this.

Answer 1

The error, name and how you describe it suggest that SCC_ROW_ADD_DTTM is a DATE column, not VARCHAR2 - which is good, because you shouldn't store dates as strings.

As it is already a date you should not do TO_DATE(SCC_ROW_ADD_DTTM, ...) or some of the other conversions you have now. Dates are stored with an internal format, and are converted to something human-readable either by an explicit TO_CHAR() or automatically by your client. It looks like your client is showing the values as MM/DD/YYYY HI:MI:SSPM when you query the table. That does not mean the column itself has that (or any) format.

You need to remove most of the conversions:

SELECT
  A.ITEM_TYPE,
  B.DESCR,
  SUM(A.ITEM_AMT - A.APPLIED_AMT),
  TO_CHAR(A.SCC_ROW_ADD_DTTM, 'YYYY'),
  TO_CHAR(CAST(A.SCC_ROW_ADD_DTTM AS TIMESTAMP), 'YYYY-MM-DD-HH24.MI.SS.FF')
FROM PS_ITEM_SF A
JOIN PS_ITEM_TYPE_TBL B
ON B.ITEM_TYPE = A.ITEM_TYPE
WHERE A.ITEM_TYPE IN ('600000050010', '600000050020', '600000050030')
AND B.EFFDT = (
  SELECT MAX(B_ED.EFFDT)
  FROM PS_ITEM_TYPE_TBL B_ED
  WHERE B.SETID = B_ED.SETID
  AND B.ITEM_TYPE = B_ED.ITEM_TYPE
  AND B_ED.EFFDT <= SYSDATE
)
GROUP BY A.ITEM_TYPE, B.DESCR, A.SCC_ROW_ADD_DTTM
HAVING SUM(A.ITEM_AMT - A.APPLIED_AMT) > 0
ORDER BY 1

fiddle

I've also switched to modern join syntax, simplified the group by clause, etc.

If your column is actually TIMESTAMP then all the above applies, just also remove the CAST() call (fiddle). Even with DATE you could remove that and add the fixed .000000 or just .0 to the format model for display.

You can also do EXTRACT(YEAR FROM ...) instead of TO_CHAR(..., 'YYYY'), which would give the result as a number instead of a string - depends which you want though.

Answer 2

Peoplesoft will do the TO_CHAR automatically, so you need to cast it back to date and then again to TO_CHAR(...,'YYYY'), thats usually how I do it using an expression.

Answer 3

PS/Query is weird but I figured out how to do it. Create an expression of type character and use substr(A.SCC_ROW_ADD_DTTM,1,4) to get the first 4 characters which is the year.

I did it with a simple PSOPRDEFN query like this:

generated this query:

SELECT A.OPRID, TO_CHAR(A.LASTSIGNONDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), 
substr( TO_CHAR(A.LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'),1,4)
FROM PSOPRDEFN A

This is the output of the year:

The key here is that the expression text "substr(A.LASTUPDDTTM,1,4)" in PS/Query is translated to substr( TO_CHAR(A.LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'),1,4) for Oracle.