CODEWITHSUNDEEP
sqloracle-databaseplsqlplsqldeveloper
Rich
Rich

Reputation: 409

PL/SQL datetime update set results in time 00:00:00

I am trying to set a datetime one second less than the end_ts datetime with sql as below

UPDATE Table1 SET 
          UPDTD_DT=(SELECT TO_DATE(END_TS-1/(24*60*60),'DD/MM/YYYY HH24:MI:SS')
          FROM View1) WHERE id = '123';

But after ran this update, the datetime is 00:00:00. I displayed and checked the TO_DATE(END_TS-1/(246060),'DD/MM/YYYY HH24:MI:SS') which looks fine.

SELECT TO_DATE(END_TS-1/(24*60*60),'DD/MM/YYYY HH24:MI:SS') FROM View1 is 25/03/2022 10:14:44

Any advice would be highly appreciated.

Upvotes: 0

Views: 342

Answers (2)

MT0
MT0

Reputation: 168041

NEVER use TO_DATE on a value that is already a DATE (or TIMESTAMP). At best, it will do nothing and, at worst, it will raise an exception or, as you are finding, give an unexpected output.

You want:

UPDATE Table1
SET   UPDTD_DT = (SELECT END_TS - INTERVAL '1' SECOND FROM View1)
WHERE id       = '123';

Or 1/(24*60*60) instead of INTERVAL '1' SECOND, but the latter is probably easier to comprehend what you mean during a code review.

If you use:

SELECT TO_DATE(END_TS-1/(24*60*60),'DD/MM/YYYY HH24:MI:SS')
FROM   View1

and END_TS is a DATE column then TO_DATE expects a string as its first argument so your DATE value will be implicitly cast to a string using the default format model for date-to-string conversions (the NLS_DATE_FORMAT session parameter) before being converted back to a DATE and the query is effectively:

SELECT TO_DATE(
         TO_CHAR(
           END_TS-1/(24*60*60),
           (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
         ),
         'DD/MM/YYYY HH24:MI:SS'
       )
FROM   View1

If the NLS_DATE_FORMAT does not match the 'DD/MM/YYYY HH24:MI:SS' format model then you will get an exception. If it does match but does not display the time component (i.e. the NLS_DATE_FORMAT is DD/MM/YYYY) then the default time component of midnight will be used.

Theoretically, you could solve the problem by changing the user's NLS_DATE_FORMAT session parameter:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

But then you would need to set it for every user who runs the query and users can change their own session parameters at any time so you would have to ensure it is set each time you run the query. So this is not considered good practice.

It is considered better practice to not use TO_DATE to a DATE value and avoid the implicit cast to a string.

fiddle

Upvotes: 6

Koen Lostrie
Koen Lostrie

Reputation: 18675

Reason that is happening is that you're doing a TO_DATE on a column of type DATE. There is no need to do that.

Check the following example (the wrapping TO_CHAR is only for display purposes):

SELECT TO_CHAR(
         TO_DATE(sysdate-1/(24*60*60),'DD/MM/YYYY HH24:MI:SS'),
         'DD-MON-YYYY HH24:MI') as unneeded_todate,
       TO_CHAR(
         sysdate-1/(24*60*60),
         'DD-MON-YYYY HH24:MI') as correct_code         
  FROM dual;

UNNEEDED_TODATE            CORRECT_CODE              
-------------------------- --------------------------
12-JUN-2023 00:00          12-JUN-2023 08:06

So, to fix your issue:

UPDATE Table1 SET 
          UPDTD_DT=(SELECT END_TS-1/(24*60*60)
          FROM View1) WHERE id = '123';

Or... use INTERVAL to add/substract.

UPDATE Table1 
   SET 
        UPDTD_DT= (SELECT END_TS - INTERVAL '1' SECOND
                     FROM View1) 
WHERE id = '123';

Upvotes: 4

Related Questions