I have two columns (Status & Updated Date) in IG. Everytime the status column is change with something, I want to log the time it is updated (in PST). Here is how I'm doing it but its not working as expected.
I have created a dynamic action on my updated date column to set the value when it is changed. However, my updated column shows some incorrect time/date.
SELECT
TO_CHAR(NEW_TIME( TO_DATE( CAST(SYSDATE as DATE), 'MM-DD-YYYY HH24:MI:SS' ), 'GMT', 'PST' ), 'MM-DD-YYYY HH24:MI:SS') TIME_IN_PST
FROM
TRANSITION_TASKS_NEW;
SYSDATE
is already a date so you do not need to use CAST
on it. Similarly, since it is already a date you do not need to use TO_DATE
on it and this is likely to cause errors.
So your code can just be:
SELECT TO_CHAR(NEW_TIME(SYSDATE, 'GMT', 'PST' ), 'MM-DD-YYYY HH24:MI:SS')
AS TIME_IN_PST
FROM DUAL;
However, you can make it even simpler using SYSTIMESTAMP
(which returns a TIMESTAMP
data type) and then you do not need to use the NEW_TIME
function:
SELECT TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'PST', 'MM-DD-YYYY HH24:MI:SS')
AS TIME_IN_PST
FROM DUAL;
Which both output:
TIME_IN_PST 03-03-2022 08:03:27
db<>fiddle here
As for what is causing your code to be incorrect:
The TO_DATE
function takes a string as the first argument. When you do:
TO_DATE(SYSDATE, 'MM-DD-YYYY HH24:MI:SS' )
Then Oracle will implicitly convert it to:
TO_DATE(
TO_CHAR(
SYSDATE,
( SELECT value
FROM NLS_SESSION_PARAMETERS
WHERE parameter = 'NLS_DATE_FORMAT')
),
'MM-DD-YYYY HH24:MI:SS'
)
If the NLS_DATE_FORMAT
session parameter is not MM-DD-YYYY HH24:MI:SS
(and the default NLS date format never matches that) then you will get either an error or, worse, an unexpected and probably invalid result.
For example:
ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-RR';
SELECT TO_CHAR(
NEW_TIME(
TO_DATE(SYSDATE, 'MM-DD-YYYY HH24:MI:SS' ),
'GMT',
'PST'
),
'MM-DD-YYYY HH24:MI:SS'
) AS TIME_IN_PST
FROM DUAL;
Outputs:
TIME_IN_PST 03-02-0022 16:00:00
Which has the wrong time component AND the wrong century!
To solve this, do not use TO_DATE
on values that are already DATE
s.
db<>fiddle here