CODEWITHSUNDEEP
oracle-databaseplsql
Akanksha Srivastava
Akanksha Srivastava

Reputation: 3

Date comparison in oracle

I have compared the dates as start date should be less than end date, but for some dates it is not validating.

I have written like this but it is throwing error for the dates and both are date columns:

Start date:'27-AUG-2023'
End Date: '26-OCT-2023'


if BUP.INS_START_DATE< BUP.INS_END_DATE,'DD-MON-YYYY' THEN
   p_message:=null;
else
   p_message:='INSURANCE START DATE MUST BE ALWAYS LESS THAN INSURANCE END DATE.';
end if;

Upvotes: 0

Views: 32

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

This:

if BUP.INS_START_DATE< BUP.INS_END_DATE,'DD-MON-YYYY' THEN

is obviously invalid; what is DD-MON-YYYY doing here, alone in the Universe?

... the dates and both are date columns

What does "date" mean here? That columns' datatype is DATE (should be), or that value represents a date, but is stored into a VARCHAR2 column (shouldn't be)?

If former (i.e. both are DATE datatypes), then you'd simply

if ins_start_date < ins_end_date then ...

If latter, then you should use to_date function with appropriate format model:

if to_date(ins_start_date, 'dd-mon-yyyy') < to_date(ins_end_date, 'dd-mon-yyyy') then ...

Otherwise, without the to_date function, you'd compare STRINGS and the result won't be correct (for example, 9-MAR-1987 will be "larger" than 23-JUN-2022 because 9 is larger than 2 (in 23)).

Upvotes: 3

Related Questions