Validate the datetime value: Difference between revisions
Jump to navigation
Jump to search
→MySQL approach
m (→MySQL approach) |
|||
| Line 4: | Line 4: | ||
== MySQL approach == | == MySQL approach == | ||
=== Verify the value is not 0000-00-00 00:00:00 === | |||
Schema of column value | |||
* Type: datetime | |||
* Default value: 0000-00-00 00:00:00 | |||
<pre> | |||
SELECT `my_date_column` | |||
FROM `my_table` | |||
WHERE `my_date_column` LIKE '0000-00-00 00:00:00'; | |||
</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 | 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 | ||
<pre> | <pre> | ||
| Line 19: | Line 33: | ||
</pre> | </pre> | ||
Verify the value should be year/month/day format | === Verify the value should be year/month/day format === | ||
<pre> | <pre> | ||
SELECT `my_date_column`, STR_TO_DATE(`my_date_column`, '%Y/%m/%d') | SELECT `my_date_column`, STR_TO_DATE(`my_date_column`, '%Y/%m/%d') | ||
| Line 28: | Line 42: | ||
</pre> | </pre> | ||
Verify the value should be hour:minute:second format e.g. {{CURRENTTIME}}:06 | === Verify the value should be hour:minute:second format === | ||
e.g. {{CURRENTTIME}}:06 | |||
<pre> | <pre> | ||
SELECT `my_time_column`, STR_TO_DATE(`my_time_column`, '%H:%i:%S') | SELECT `my_time_column`, STR_TO_DATE(`my_time_column`, '%H:%i:%S') | ||
| Line 37: | Line 52: | ||
</pre> | </pre> | ||
Verify the value should be year-month-day hour:minute:second format e.g. {{Template:Today}} {{CURRENTTIME}}:06 | === Verify the value should be year-month-day hour:minute:second format === | ||
e.g. {{Template:Today}} {{CURRENTTIME}}:06 | |||
<pre> | <pre> | ||
SELECT `my_time_column`, STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S') | SELECT `my_time_column`, STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S') | ||
| Line 47: | Line 63: | ||
Verify the value not start with four number, which means year | === Verify the value not start with four number, which means year === | ||
{{exclaim}} Not recommended! | |||
<pre> | <pre> | ||
SELECT `my_date_column` | SELECT `my_date_column` | ||