I'm trying to write an expression in PSQuery which will subtract two dates from each other and return the difference.
If I write the following in SQL it works fine, Select ROUND(A.EFFDT - A.LASTUPDDTTM) from PS_JOB a.
However if I try to insert this as an expression in PSQuery I get various error messages about Date formats beingincorrect.
I think the problem is the dynamic SQL that's created by PSQuery, i tried added a To_Date conversion into the expression but PS still wirting the SQL as follows:-
SELECT TO_DATE( TO_CHAR(A.EFFDT,'YYYY-MM-DD'),'YYYY-MM-DD') - (TO_DATE( TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD-HH24-MI-SS-FF'))
If I don't include the .SS.FF in the mask I receive an error about the date picture, ORA1830 error.
Does anybody have any experience of using the last update field on job in HRMS. Or can anybody suggest how I can write the expression in ps query.