User defined functions in MySql - check date format and leap year in MySql UDF

The below function can be used to check the date format (MMDDYYYY) in any input column. Also, it checks for a valid date. So, if for example, there is a date 02302014 (30th Feb, 2014), it will return 0.

0 = False (Not a valid date)
1 = True (Is a valid date)

While compiling, please change the DEFINER=`dataanalysis` to a valid user in your system. Else, you will get a user not present definer error.

The error message will be:
0 row(s) affected, 1 warning(s): 1449 The user specified as a definer ('dataanalysis'@'%') does not exist

Example code for Date validation:

DELIMITER $$
CREATE FUNCTION fn_str_date_chk(column_value Varchar(200)) RETURNS tinyint(1)
BEGIN
 DECLARE is_valid BOOLEAN default 0;
 #declare continue handler for sqlwarning

if char_length(column_value) <> 8 then
set is_valid = 0;
elseif char_length(column_value) = 8 then

case when date(str_to_date(column_value,'%m%d%Y')) is null then
set is_valid = 0;
else set is_valid = 1;
end case;
else
set is_valid = 1;
end if;

return is_valid;
end$$
DELIMITER ;

Usage:

select fn_is_date(date_of_birth) from employee;

Result:

0 = for all invalid date of birth
1 = for all valid date of birth

Please like us if you appreciate our blog. And make sure to chip in with your suggestions.