Add number of days in Oracle DB using SQL in java code-CodePudding

I am using below SQL in java code to update date in Oracle DB.

UPDATE CUS_LOGS SET START_DATE=to_date(systimestamp   3,'DD-MON-RRRR'), END_DATE=to_date(systimestamp   21921,'DD-MON-RRRR')  
WHERE CUS_ID IN ('9b90cb8175ba0ca60175ba12d8711006');

My expectation is:

START_DATE = 05-NOV-2022 
END_DATE = 08-NOV-2082.

However the result I see as

START_DATE = 05-NOV-2022 
END_DATE = 08-NOV-1982    **********

CodePudding user response：

The problem is that you're relying on implicit conversion, which is using your NLS settings, which are formatting dates as string with 2-digit years.

If your NLS_DATE_FORMAT is DD-MON-RR or DD-MON-YY then doing

to_date(systimestamp   3,'DD-MON-RRRR')

is really doing something like:

to_date(to_char(cast(systimestamp as date)   3,'DD-MON-RR','DD-MON-RRRR')

which means it tries to convert the string '08-NOV-82' to a date using the mask 'DD-MON-RRRR'. The RRRR means that a 2-digit year like 82 is interpreted as 1982 - which is what you're seeing. Using DD-MON-YYYY would be even worse, as that would interpret 82 as 0082.

alter session set nls_date_format = 'DD-MON-RR'

select
  to_date(sysdate   3,'DD-MON-RRRR') as a,
  to_char(to_date(sysdate   3,'DD-MON-RRRR'), 'YYYY-MM-DD') as b,
  to_date(sysdate   21921,'DD-MON-RRRR') c,
  to_char(to_date(sysdate   21921,'DD-MON-RRRR'), 'YYYY-MM-DD') as d,
  to_date(sysdate   3,'DD-MON-YYYY') as e,
  to_char(to_date(sysdate   3,'DD-MON-YYYY'), 'YYYY-MM-DD') as f,
  to_date(sysdate   21921,'DD-MON-YYYY') as g,
  to_char(to_date(sysdate   21921,'DD-MON-YYYY'), 'YYYY-MM-DD') as h
from dual

A	B	C	D	E	F	G	H
05-NOV-22	2022-11-05	08-NOV-82	1982-11-08	05-NOV-22	0022-11-05	08-NOV-82	0082-11-08

So you would need to explicitly convert the date to a string with a 4-digit year, then convert it back again...

But you don't need to do any of that conversion. Just adding the number of days to the timestamp - which implicitly converts that to a date, but safely - gets the result you want:

alter session set nls_date_format = 'YYYY-MM-DD'

alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS TZR'

select systimestamp,
  systimestamp   3,
  systimestamp   21921
from dual

SYSTIMESTAMP	SYSTIMESTAMP 3	SYSTIMESTAMP 21921
2022-11-02 10:42:24 00:00	2022-11-05	2082-11-08

There's no point using systimestamp here though, you can avoid even that implicit conversion by using sysdate:

select sysdate,
  sysdate   3,
  sysdate   21921
from dual

SYSDATE	SYSDATE 3	SYSDATE 21921
2022-11-02	2022-11-05	2082-11-08

fiddle

You seem to be doing the conversion in order to remove the time portion of the current date/time - or, more correctly, set it to midnight as a date always has a time. But you can do that with trunc().

So, in summary, your update statement should just be:

UPDATE CUS_LOGS SET START_DATE=trunc(sysdate)   3, END_DATE=trunc(sysdate)   21921
WHERE CUS_ID IN ('9b90cb8175ba0ca60175ba12d8711006');

Your end date us roughly 60 years ahead; you might want to use add_months(trunc(sysdate), 60*12) and then add any additional days to that - maybe just keeping the 3, inside or outside the add_months(), or both; add_months(trunc(sysdate) 3, 60*12) 3 would give the same result as 21921, today anyway. But it isn't clear exactly what you're doing...

And you can add intervals rather than a number of days, which is a bit more explicit; but that works less well for adding months/years because it can lead to invalid-date errors, e.g. if you try to add a year interval to Feb 29th.

CodePudding user response：

You can simplify the query and prevent issues from implicit timestamp-to-date-to-string conversions (as described by @Alex Poole) using:

UPDATE CUS_LOGS
SET   START_DATE = TRUNC(SYSDATE)   INTERVAL '3' DAY,
      END_DATE   = ADD_MONTHS(TRUNC(SYSDATE), 60*12)   INTERVAL '6' DAY 
WHERE CUS_ID     = '9b90cb8175ba0ca60175ba12d8711006';