Requirement: I want to validate if the user provided Date value is in DD-MM-YYYY format.

Solution: For the solution, of the above requirement let us create an Oracle Function validate_date_format. The complete code for the validate_date_format is as below:

CREATE OR REPLACE FUNCTION validate_date_format (
    i_date VARCHAR2
) RETURN VARCHAR2 IS
    lv_date DATE;
BEGIN
    SELECT
        to_date(substr(i_date, 0, 11), 'DD-MM-YYYY')
    INTO lv_date
    FROM
        dual;

    RETURN 'Valid Date Format';
EXCEPTION
    WHEN OTHERS THEN
        RETURN 'Invalid Date Format';
END;

Now, let us validate the result of the above function.

For that let us first pass the Date value as 11-24-2020, and check the result.

SELECT validate_date_format('11-24-2020') result FROM dual;

And the result of the above SQL Query is as below:

Now, let us first pass the Date value as 24-10-2020, and check the result.

SELECT validate_date_format('24-10-2020') result FROM dual;

And the result of the above SQL Query is as below:

Hence, the solution to our requirement.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 1,165 total views,  4 views today