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

Question

I have one input DATE parameter for ex procedure: IN p_date DATE. I want to check this format for DATE input data inside a procedure, which should be in the format YYYY-MM-DD. If the input parameter has characters or the date format is incorrect, it must use SIGNAL through the exception.

Please find below the code I wrote

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

Thank you Sagar

Answer 1

TL DR

The regular expression will not work in this case, because they can only check the date format, but not if it is a valid date (for example, it '2014-02-30'has the correct format, but the data for the date is invalid)

Use string functions

Concept

- , . , - , , . - - . - , . , , .

:

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

, , :

• -, ( -). , .
• -, , (, foo-bar-baz). , .
• -, , , , , - (, 2014-13-01 ).

,

STR_TO_DATE(), . , , (, 2014-1-1) - , . .

YYYY-MM-DD , MySQL, . , '0001-01-01' , :

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

, , , MySQL. , , 001-01-01, , MySQL:

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)

- , YYYY-MM-DD, , .