0

I could fix this issue by declaring the variable as DATE and not as VARCHAR.

I have a pre-configured constant varchar2 value in a table.

I have declared lv_constants_config as VARCHAR2 and not as DATE.

I think that is the reason why I am facing problem.

SELECT * FROM constants_config

------------------------------
constant1
---------
01/01/3000

In my PL/SQL code I am retrieving this value as,

SELECT TO_DATE(constant1,'MM/DD/YYYY') --I also tried with RRRR instead of YYYY.
INTO lv_constants_config
FROM constants_config;

I am comparing this value with the value input by the User (from UI).

IF iv_input_date > lv_constants_config 
THEN
  --do this
ELSE
 --do that
END IF;

My problem is 3000 is getting wrongly interpreted as 00.

Eg., 01/01/3000 is getting interpreted as 1st Jan, 2000

01/01/3049 is getting interpreted as 1st Jan, 2049

01/01/3050 is getting interpreted as 1st Jan, 2050

How can I compare dates with century value as it is?

user2488578
  • 896
  • 4
  • 21
  • 40
  • 3
    Please [edit] your question with an executable PL/SQL block (rather than snippets that we cannot run). The problem is probably going to be with the declaration of the `iv_input_date` variable or to do with how you assign the variables but you have not given those pieces of code. So please help us to help you and give us a complete [MRE] that we can execute to replicate the issue. – MT0 Apr 21 '21 at 14:03
  • 1
    Cannot replicate the problem [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=3d69a169fe956e15bb4cb688bccd2dbf) – MT0 Apr 21 '21 at 14:08
  • 2
    Are you sure you have `lv_constants_config` as a string, not `iv_input_date`? It isn't clear whether 01/01/3049 etc are different `constant1` values, or `iv_input_date` values. Please expand your snippet to a [mcve] as MTO said, and also include your session NLS_DATE_FORMAT setting. – Alex Poole Apr 21 '21 at 14:46
  • It sounds like you think the issue is your `lv_constants_config` variable. How have you decided that? What's the output of `select to_char(to_date(TO_DATE(constant1,'MM/DD/YYYY'), 'mm/dd/yyyy') from constants_config;`? That should show you the issue isn't the conversion of the string to a date. How is your user input being passed in? This seems more likely to me to be the source of whatever issue you're seeing. – Boneist Apr 21 '21 at 15:24

0 Answers0