Validate the datetime value: Difference between revisions
Jump to navigation
Jump to search
(Created page with "Verify the value by using MySQL functions: Verify the value should be year-month-day format e.g. {{Template:Today}} == MySQL approach == <pre> SELECT `my_date_column`, UNIX_...") |
mNo edit summary |
||
Line 1: | Line 1: | ||
Validate the value should be year-month-day format e.g. {{Template:Today}} | |||
== MySQL approach == | == MySQL approach == |
Revision as of 17:29, 13 August 2020
Validate the value should be year-month-day format e.g. 2024-03-28
MySQL approach
SELECT `my_date_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y-%m-%d')) FROM `my_table` WHERE UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y-%m-%d')) IS NULL;
Verify the value should be year/month/day format
SELECT `my_date_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y/%m/%d')) FROM `my_table` WHERE UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y/%m/%d')) IS NULL;
Verify the value should be hour:minute:second format e.g. 11:31:06
SELECT `my_time_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S')) FROM `my_table` WHERE UNIX_TIMESTAMP(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. 2024-03-28 11:31:06
SELECT `my_time_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S')) FROM `my_table` WHERE UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S')) IS NULL;
Alternative PHP solution: strtotime