Validate the datetime value

From LemonWiki共筆
Jump to navigation Jump to search

Validate the value should be well-formated value e.g. year-month-day 2020-10-29

MySQL approach[edit]

SELECT `my_date_column`, STR_TO_DATE(`my_date_column`, '%Y-%m-%d')
FROM `my_table`
WHERE
STR_TO_DATE(`my_date_column`, '%Y-%m-%d') IS NULL;

Verify the value should be year/month/day format

SELECT `my_date_column`, STR_TO_DATE(`my_date_column`, '%Y/%m/%d')
FROM `my_table`
WHERE
STR_TO_DATE(`my_date_column`, '%Y/%m/%d') IS NULL;

Verify the value should be hour:minute:second format e.g. 01:16:06

SELECT `my_time_column`, STR_TO_DATE(`my_time_column`, '%H:%i:%S') 
FROM `my_table`
WHERE
STR_TO_DATE(`my_time_column`, '%H:%i:%S') IS NULL;

Verify the value should be year-month-day hour:minute:second format e.g. 2020-10-29 01:16:06

SELECT `my_time_column`, STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S') 
FROM `my_table`
WHERE
STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S') IS NULL;

PHP appracoh[edit]

Using PHP strtotime function

Excel approach[edit]

  1. Validate the total length of cell value is equal to 10 e.g. =IF(LEN(B2)<>10, TRUE, FALSE)
  2. Validate the positions of the - symbols
  3. Combined the above condition, the verify formula is: (If the cell value is well-formatted, the formula returns true)
=NOT(
     IF(OR(
           ISERROR(FIND("-", B2, 1)), 
           ISERROR(FIND("-", B2, 6)), 
           IF(LEN(B2)<>10, TRUE, FALSE)
           ), TRUE, FALSE)
 )

References[edit]