Validate a Date

This function validates a date and returns a boolean value. The date must be in one of these formats:

d-mmm-yy

dd-mmm-yy

d-mmm-yyyy

dd-mmm-yyyy

It will return either True for a valid date or False for an invalid date. The month can be capitalized or not.

An example of a call to the function:

Set Serveroutput On
Begin
   Dbms_Output.Put_Line(Case When ValDt('&test_Date')
                             Then 'This is valid' 
                             Else 'This is invalid' 
                         End
                       );
End;
/

rem +--------------------------------------------------------------------------+ rem | Script ID: ValDt.sql rem | rem | Purpose: This function is looking for a date in one of these formats: rem | d-mmm-yy dd-mmm-yy d-mmm-yyyy dd-mmm-yyyy rem | It will return either True for a valid date or rem | False for an invalid date rem | rem | Developer: Lynn Tobias rem | Script Date: 11/14/2008 rem | Oracle Ver: 10g rem +--------------------------------------------------------------------------+ Create Or Replace Function ValDt(Test_Date Varchar2) Return Boolean As v_Return Boolean ; v_Day Number(2) ; v_Mth Varchar2(3); v_Yr Number(4) ; Begin v_Day := Substr(Test_Date,1,Instr(Test_Date,'-',1,1) -1 ) ; v_Mth := Substr(Test_Date,Instr(Test_Date,'-',1, 1 ) +1 ,Instr(Test_Date,'-',1, 2 ) -Instr(Test_Date,'-',1, 1 ) -1 ) ; v_Yr := Substr(Test_Date,Instr(Test_Date,'-',1, 2 ) +1 ) ; v_Return := Case When v_Day Between 1 And To_Char( Last_Day('01-'||v_Mth||'-'||v_Yr) ,'dd' ) Then True Else False End; Return v_Return; End; /