1

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.

James Z
  • 12,209
  • 10
  • 24
  • 44
cody johnson
  • 21
  • 2
  • 2
    The error message "ORA-01843: not a valid month" typically occurs when the input string to the TO_DATE() function does not match the specified date format. One possibility is that some of the values in the SCC_ROW_ADD_DTTM column have unexpected values for the month portion, causing the TO_DATE() function to fail. – Tushar Feb 16 '23 at 17:24
  • 2
    `ORA-` sounds like oracle not mysql? – Jens Feb 16 '23 at 17:25
  • Can you please share few records for this column `SCC_ROW_ADD_DTTM` – Tushar Feb 16 '23 at 17:25
  • Also assuming this is Oracle (from error message) Can you try to use `RRRR` format (which is more flexible and can handle a wider range of years.) instead of `YYYY` – Tushar Feb 16 '23 at 17:28
  • You could also use the SUBSTR() function to extract the month portion of the string and check if it is within the range of 01-12. `SELECT SUBSTR(A.SCC_ROW_ADD_DTTM, 1, 2) AS MONTH_STRING FROM PS_ITEM_SF A WHERE SUBSTR(A.SCC_ROW_ADD_DTTM, 1, 2) NOT BETWEEN '01' AND '12'` – Tushar Feb 16 '23 at 17:29
  • 1
    What data type is `SCC_ROW_ADD_DTTM`? If "from a DateTm field" means that it is DATE or TIMESTAMP then you should *not* call `to_date()` for it as it will to implicit conversion first/ (Your client show you values in that format but that doesn't matter.) All the `cast`ing is probably wrong, or at least unnecessary, too - but that perhaps also implies the column is DATE? – Alex Poole Feb 16 '23 at 17:50
  • 1
    @Tushar the SUBSTR function finally worked for me to get the desired result, I initially tried "SUBSTRING" and received an error as that is not a valid function in this software. – cody johnson Feb 16 '23 at 18:39
  • @AlexPoole the field has a "Format" of DateTm but I now see that the query manager auto casts it to a time stamp in the back end for whatever reason, making it nearly impossible to work with. I cannot prevent it from cast ing. – cody johnson Feb 16 '23 at 18:40
  • 1
    What data type is the table column? "DateTm" is not an Oracle data type so don't know where you are seeing that or what it means. But like I said in another comment, casting isn't necessarily a problem (unless it is stored as a string, which hopefully it isn't). – Alex Poole Feb 16 '23 at 18:45
  • @AlexPoole in Query Manager on the fields tab there is a list of all fields included in the query and their "Format". I was under the impression this was a data type but if that is not the case, there is no other way to find the data type of the field as I can't edit the raw SQL. – cody johnson Feb 16 '23 at 18:49
  • *if that is not the case, there is no other way to find the data type of the field as I can't edit the raw SQL* - `dump(acc_row_add_dttm)` would tell you. Type 12 is a date, 180 is a timestamp, 1 is a varchar2 etc. – William Robertson Feb 18 '23 at 10:20

3 Answers3

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thank you for this answer. It's helped me realize how limiting query manager is as I can't make an expression using EXTRACT, and am unable to edit the raw SQL to remove the cast functions which are automatically applied to all DateTm fields which sucks. I'll just have to accept that it is not possible as is. – cody johnson Feb 16 '23 at 18:34
  • 1
    Well, if it's a DATE then casting doesn't really matter, it just isn't necessary - except for the call where you extract the fractional seconds. It will work [with all references cast](https://dbfiddle.uk/YQu2muTn). It's `TO_DATE()` that's breaking it. Why does it cast SCC_ROW_ADD_DTTM but not EFFDT? Are they different data types? – Alex Poole Feb 16 '23 at 18:38
  • I can confirm that EFFDT has a format of "Date" from what I can see but I also believe that it doesn't apply the cast function as on the backend it automatically joined the descriptions from another table. I hate query manager since it tries to make things simpler for your average workers by preventing them from coding but as a result it's incredibly limiting and unclear. – cody johnson Feb 16 '23 at 18:45
  • 1
    I sympathise, I've never seen the point and would much rather write native SQL. But again the casting shouldn't matter, as long as you remove the unnecessary `TO_DATE()` and `TO_CHAR()` calls. I'm unclear how much of the query you can influence, but the question implied you were at least doing one of those yourself. – Alex Poole Feb 16 '23 at 19:01
1

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.

Walucas
  • 2,549
  • 1
  • 21
  • 44
0

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:

enter image description here

enter image description here

enter image description here

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:

enter image description here

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.

Bobby Durrett
  • 1,223
  • 12
  • 19