Validate the datetime value: Difference between revisions
Jump to navigation
Jump to search
mNo edit summary |
mNo edit summary |
||
| Line 1: | Line 1: | ||
Validate the value should be well-formated value e.g. year-month-day {{Template:Today}} | Validate the value should be well-formated value e.g. year-month-day {{Template:Today}} | ||
__TOC__ | |||
== MySQL approach == | == MySQL approach == | ||
| Line 40: | Line 42: | ||
Using PHP [https://www.php.net/manual/en/function.strtotime.php strtotime] function | Using PHP [https://www.php.net/manual/en/function.strtotime.php strtotime] function | ||
== | == References == | ||
<references/> | <references/> | ||
[[Category:Spreadsheet]] [[Category:Excel]] | [[Category:Spreadsheet]] | ||
<!-- [[Category:Excel]] --> | |||
[[Category:Data_hygiene]] | [[Category:Data_hygiene]] | ||
[[Category:Data Science]] | [[Category:Data Science]] | ||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category:Text file processing]] | [[Category:Text file processing]] | ||
Revision as of 17:36, 13 August 2020
Validate the value should be well-formated value e.g. year-month-day 2026-05-06
MySQL approach
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. 16:10: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. 2026-05-06 16:10: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
Using PHP strtotime function