How to validate input date in YYYY-MM-DD format inside the MySQL procedure

Question

I have one DATE input parameter for a procedure ex: IN p_date DATE.I want to validate this input DATE parameter format inside a procedure which should be in YYYY-MM-DD format. If the input parameter is having characters or date format is wrong it should through an exception using SIGNAL.

Please find the below code what i written

CREATE PROCEDURE `validation_check`(IN pdate_time DATE)
BEGIN


DECLARE InputValidation CONDITION FOR SQLSTATE '45000';
DECLARE dateValidation CONDITION FOR SQLSTATE '45000';

/*  Doing NULL validation  */
IF pdate_time IS NULL THEN
SIGNAL InputValidation
SET MESSAGE_TEXT='pdate_time should not be empty.';
END IF;

/*  Doing Date format validation
IF STR_TO_DATE(pdate_time,'%Y-%m-%d') != pdate_time THEN
SIGNAL dateValidation
SET MESSAGE_TEXT='Input Date format should be in YYYY-MM-DD.';
END IF;
*/
/*  Doing Date format validation  */
IF pdate_time NOT REGEXP '/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/' THEN
SIGNAL dateValidation
SET MESSAGE_TEXT='Input Date format should be in YYYY-MM-DD.';
END IF;

SELECT pdate_time;

END

Thanks, Sagar

Answer 1

TL;DR

Regular expression will fail in this case, because they may only check date format, but not if it is a valid date (as, for example, '2014-02-30' has correct format but invalid data for date)

Use string functions

Concept

The solution is - yes, use string functions. However, regular expressions will also be useful - to check if format was good, you still need to check date itself. Since validation of date is a single separated issue - you should create a function for it. That function will accept string and return result as boolean value - so either date is ok or not. This will be re-usable and, therefore, more flexible.

Code

Here we go with the function:

CREATE FUNCTION VALIDATE_DATE(d VARCHAR(255))
RETURNS INT
BEGIN
   DECLARE date_year  VARCHAR(255) DEFAULT '';
   DECLARE date_month VARCHAR(255) DEFAULT '';
   DECLARE date_day   VARCHAR(255) DEFAULT '';
   DECLARE ym_delim   INT DEFAULT 0;
   DECLARE md_delim   INT DEFAULT 0;
   -- First, if it's just not xxx-yyy-zzz format:
   SET ym_delim = LOCATE('-', d);
   SET md_delim = LOCATE('-', d, ym_delim+1);
   IF !ym_delim || !md_delim THEN

      RETURN FALSE;
   END IF;
   -- Second, if resulted members are not YYYY, MM or DD:
   SET date_year  = SUBSTR(d, 1, ym_delim-1);
   SET date_month = SUBSTR(d, ym_delim+1, md_delim-ym_delim-1);
   SET date_day   = SUBSTR(d, md_delim+1);
   IF  date_year  NOT REGEXP '^[0-9]{4}$'
    || date_month NOT REGEXP '^[0-9]{2}$'
    || date_day   NOT REGEXP '^[0-9]{2}$' THEN
      RETURN FALSE;
   END IF;
   -- Finally, check if date itself is ok, like 2014-02-30 isn't ok:
   IF DATE(CONCAT(date_year, '-', date_month, '-', date_day)) IS NULL THEN
      RETURN FALSE;
   END IF;
   RETURN TRUE;
END//
DELIMITER ;

As you can see, we have three cases, when date validating fails:

• First, if there are no proper delimiters (which are -). Then year, month and day just can't be found
• Second, if extracted year, month and day part are just bad (for instance, date was foo-bar-baz). That's why we can't use date functions to extract those parts and so we have to use string functions.
• Third - finally, if our date parts seems to be good, there still may be false result because of invalid combination (2014-13-01 has wrong month, for example).

Seems to be a solution

There is, however, STR_TO_DATE() function which may look like solution. Unfortunately, it will pass date parts which are not in corresponding format (such as 2014-1-1) - thus, it can't be used for direct format validation. That is why I used separate stored function instead.

What will be passed

All YYYY-MM-DD dates, which are correct in terms of MySQL, will be passed. That is, early dates, such as '0001-01-01' are correct :

mysql> SELECT VALIDATE_DATE('0001-01-01');
+-----------------------------+
| VALIDATE_DATE('0001-01-01') |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

And, in fact, they should be correct, because they are valid for MySQL. However, such things as 001-01-01 won't be passed even despite fact, that such strings are correct for MySQL dates too:

mysql> SELECT VALIDATE_DATE('001-01-01'), DATE('001-01-01');
+----------------------------+-------------------+
| VALIDATE_DATE('001-01-01') | DATE('001-01-01') |
+----------------------------+-------------------+
|                          0 | 0001-01-01        |
+----------------------------+-------------------+
1 row in set (0.00 sec)

And that is derived from your format expectations - you should filter all the things, which do not have YYYY-MM-DD format exactly, thus, you'll have such results.