CODEWITHSUNDEEP
mysqldate
Iain Fifer
Iain Fifer

Reputation: 123

MySQL String to Date (Generic)

Can anyone suggest a function that converts strings to date?

> This Function will takes a string and give:
> 0000-00-00 if string cannot be converted (string in <> date out) 
> NULL if String NULL or blank(string in = date out) 
> yyyy-mm-dd if string can be converted (string in = date out)

Upvotes: 1

Views: 228

Answers (1)

Iain Fifer
Iain Fifer

Reputation: 123

> This Function will takes a string and give:
> 0000-00-00 if string cannot be converted (string in <> date out) 
> NULL if String NULL or blank(string in = date out) 
> yyyy-mm-dd if string can be converted (string in = date out)

Drop function if exists stringtodate;

DELIMITER $$

CREATE FUNCTION `stringtodate`(v TEXT) Returns DATE 
BEGIN
DECLARE result DATE;

IF (v Is Null or v = '') THEN SET result = NULL;
ELSEIF (STR_TO_DATE(v,'%d-%m-%Y') IS NOT NULL AND length(clean(v)) > 8) THEN SET result = STR_TO_DATE(v,'%d-%m-%Y');
ELSEIF (STR_TO_DATE(v,'%d,%m,%Y') IS NOT NULL AND length(clean(v)) > 8) THEN SET result = STR_TO_DATE(v,'%d,%m,%Y');
ELSEIF (STR_TO_DATE(v,'%d/%m/%Y') IS NOT NULL AND length(clean(v)) > 8) THEN SET result = STR_TO_DATE(v,'%d/%m/%Y');
ELSEIF (STR_TO_DATE(v,'%Y-%m-%d') IS NOT NULL AND length(clean(v)) > 8) THEN SET result = STR_TO_DATE(v,'%Y-%m-%d');
ELSEIF (STR_TO_DATE(v,'%Y,%m,%d') IS NOT NULL AND length(clean(v)) > 8) THEN SET result = STR_TO_DATE(v,'%Y,%m,%d');
ELSEIF (STR_TO_DATE(v,'%Y/%m/%d') IS NOT NULL AND length(clean(v)) > 8) THEN SET result = STR_TO_DATE(v,'%Y/%m/%d');
ELSEIF (STR_TO_DATE(v,'%d-%m-%y') IS NOT NULL AND length(clean(v)) < 10) THEN SET result = STR_TO_DATE(v,'%d-%m-%y');
ELSEIF (STR_TO_DATE(v,'%d,%m,%y') IS NOT NULL AND length(clean(v)) < 10) THEN SET result = STR_TO_DATE(v,'%d,%m,%y');
ELSEIF (STR_TO_DATE(v,'%d/%m/%y') IS NOT NULL AND length(clean(v)) < 10) THEN SET result = STR_TO_DATE(v,'%d/%m/%y');

ELSE SET result = CAST("0000-00-00" AS DATE);

END IF;

Return result;

END

Upvotes: 1

Related Questions