On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Friday, November 14, 2008

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;
    /

    No comments:

    Post a Comment