MySQL expiration date

Question

Does MySQL provide any function that checks for the correct date? The DATE function returns NULL when providing an invalid date 2013-02-30, for example. However, I also use STR_TO_DATE at the same time, which mysteriously stops DATE from working properly.

 SELECT DATE('2013-02-30'); NULL SELECT STR_TO_DATE('2013-02-30', '%Y-%m-%d'); NOT NULL SELECT DATE('2013-02-40'); NULL SELECT STR_TO_DATE('2013-02-40', '%Y-%m-%d'); NULL SELECT DATE(STR_TO_DATE('2013-02-30', '%Y-%m-%d')); NOT NULL 

Why is the STR_TO_DATE function halt DATE and is there some workaround to check if the date is valid when using STR_TO_DATE (which I must use)?

I came across an answer in the meantime: apparently the DATE function skips several validation checks when the data type is already of the date type ( STR_TO_DATE converts the data types to date). Therefore, converting the date to a string after parsing it in the correct format using STR_TO_DATE does the trick:

@valid_date = NOT ISNULL(DATE(CONVERT(STR_TO_DATE('2013-02-29', '%Y-%m-%d'), CHAR))) .

Answer 1

It is very difficult to check if a field is a date due to all the different possible date formats that need to be considered. BUT, if you know that field date formats are one of the following:

 'yyyy-mm-dd' 'yyyy-mm-dd hh:mm:ss' 'yyyy-mm-dd whatever' 

This code will help you:

  SELECT count(*) FROM `table` WHERE DATE(STR_TO_DATE(`column`, '%Y-%m-%d')) IS NOT NULL AND `column` NOT REGEXP '^[0-9\.]+$' 

Basically:

• the first condition tells you whether the date is, but, unfortunately, does not exclude numbers (for example: DATE(STR_TO_DATE(**1**, '%Y-%m-%d')) = '2001-00-00'
• the second ensures that numbers are excluded, leaving you only with dates that follow the formats above.

If count(*) - >0 , then this is the date, if it is 0 something else.

Note This method works for strings of any date format if you know in advance in which format they follow (which I know is not always the case, but still useful). Just replace the a priori format with STR_TO_DATE

Answer 2

I can’t clearly understand your goal, maybe this is an idea.
SELECT DATE_FORMAT (yourFiled, '% Y-% m-% d') days FROM yourTable GROUP BY days; it is not null; you can change it. some kind

 SELECT DATE_FORMAT(yourFiled, '%Y-%m-%d') days FROM yourTable WHERE yourFiled > '2013-9-23 00:00:00' GROUP By days; 

Answer 3

Try the following:

SELECT DATE(STR_TO_DATE('2013-00-30', '%Y-%m-%d')); --is also NOT NULL