Tue. Mar 9th, 2021

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

 337 total views,  1 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

2 thoughts on “Oracle Function to Validate if the Input Date is in DD-MM-YYYY Format”
  1. This function wont be able to validate something like this
    SELECT validate_date_format(’01-05-2020′) result FROM dual;
    RESULT
    Valid Date Format
    what if as shown above 01 is month and not day, is there any way to validate this?

    1. Hi,
      Can you please elaborate on what actually you are trying to validate?
      Well, if we see at the function it clearly says that 01 is a Month and not a Date.
      Thanks & Regards,
      Susanto Paul

Leave a Reply

Your email address will not be published. Required fields are marked *