How to compare centuries in Oracle Database?

51 Views Asked by At

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?

0

There are 0 best solutions below