Editing Validate the datetime value

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_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;

</pre>

Verify the value should be year/month/day format
<pre>
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;

</pre>

Verify the value should be hour:minute:second format e.g. {{CURRENTTIME}}:06
<pre>
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;

</pre>

Verify the value should be year-month-day hour:minute:second format e.g. {{Template:Today}} {{CURRENTTIME}}:06
<pre>
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;

</pre>

Alternative PHP solution: [https://www.php.net/manual/en/function.strtotime.php strtotime]

== references ==
<references/>

[[Category:Spreadsheet]] [[Category:Excel]]
[[Category:Data_hygiene]]
[[Category:Data Science]]
[[Category:MySQL]]
[[Category:Text file processing]]