Converting a timestamp to a date in PeopleSoft Query seems to be a common item, which I have helped others with, to the point that I kept a personal note on the coding logic. So, I thought I would post this quick note in case it may be useful to someone. This item would seem to be straight forward, however, PS query tries to help you with the process, so, some experimentation is usually involved to get the code to out just right.
Here is a sample expression I use for the timestamp to date conversion in PS Query.
- Create an expression with an Expression Type of “Number”
- Set Length to 10
- Sample code: TO_DATE(SUBSTR(<<RecordAlias.TimestampFieldName>>,1,10))
- PS Query interpretation: TO_DATE(SUBSTR(TO_CHAR(CAST((<<RecordAlias.TimestampFieldName>>) AS TIMESTAMP),’YYYY-MM-DD-HH24.MI.SS.FF’),1,10))
- PS Query Migration Validation SQLs
- How to Add User Identification to a PeopleSoft Query?
- PeopleSoft XLAT Value Lookup SQL