TO CHECK THE DATE FORMAT IN TABLE USING IF CONDITION

Question

I need to check whether the dates in a table are in the format of DD-MON-YYYY and if it is not then need to raise exception ..Please help me as i am new to this field.

Answer 1

How about:

to_date(yourDateColumn,'DD-MON-YYYY')

assuming yourDateColumn is a string (varchar2 or char or whatever). to_date will throw an exception if the date is not valid.

If you really need to use if then you could implement a function that will return 1 if the date is valid and 0 otherwise say:

function isValidDate(yourDate in varchar2) return number is 
 tempDate Date;  
begin 
  tempDate := to_date(yourDate,'DD-MON-YYYY');
  return 1;
exception when others then 
  return 0;
end;

And then use the function in IF statement

if isValidDate(yourDate in Date)=1 then 
--perform some operations 
end if;

However, keep in mind that it is a bad practice to store dates as a sting in a database.