Editing Validate the datetime value (section)

== MySQL approach ==

=== Verify the value is not 0000-00-00 00:00:00 ===
Schema of column value

* Type: {{kbd | key=datetime}}
* Default value: 0000-00-00 00:00:00 (NOT allow be NULL)、"1999/12/31 上午 12:00:00" on Google sheet

<pre>
SELECT `my_date_column`
FROM `my_table`
WHERE `my_date_column` LIKE '1900-%';
</pre>

=== Verify the year of value is not 1900 or 1999 years ===
Schema of column value

* Type: {{kbd | key=VARCHAR}}
* Default value: allow be NULL

Abnormal values e.g. {{kbd | key=1900-01-08 00:00:00}} because the Excel datetime issue<ref>[https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487 Date systems in Excel - Microsoft Support]</ref>.

<pre>
SELECT `my_date_column`
FROM `my_table`
WHERE `my_date_column` LIKE '1900-%';
</pre>

Or list the year list
<pre>
SELECT 
DISTINCT LEFT(`my_date_column`, 4)
FROM `my_table`
ORDER BY LEFT(`my_date_column`, 4)
LIMIT 50
</pre>

=== Verify the value is null ===
Verify the value of {{kbd | key=DAY()}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions]</ref> is NULL

Schema of column value

* Type: {{kbd | key=datetime}} or {{kbd | key=VARCHAR}}
* Default value: allow be NULL

<pre>
SELECT `my_date_column`
FROM `my_table`
WHERE DAY(`my_date_column`) IS NULL;
</pre>

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

</pre>

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

</pre>

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

</pre>

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

</pre>


=== Verify the value not start with four number, which means year ===

{{exclaim}} Not recommended!
<pre>
SELECT `my_date_column`
FROM `my_table`
WHERE `my_date_column` NOT REGEXP '^[0-9]{4}';
</pre>