14,974
edits
Validate the datetime value: Difference between revisions
Jump to navigation
Jump to search
→Verify the date_column is a valid date e.g. YYYY-MM-DD
No edit summary |
|||
| (18 intermediate revisions by the same user not shown) | |||
| Line 4: | Line 4: | ||
== MySQL approach == | == MySQL approach == | ||
=== Verify the column value is a valid (non-zero) datetime e.g. 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 date_column is a valid date e.g. YYYY-MM-DD === | |||
Schema of column date_column | |||
* Type: {{kbd | key=varchar}} or {{kbd | key=date}} | |||
* Expected format: YYYY-MM-DD | |||
Schema SQL: | |||
<pre> | |||
CREATE TABLE sample_date_validation ( | |||
date_column VARCHAR(20), | |||
is_abnormal BOOLEAN, | |||
comments VARCHAR(100) | |||
); | |||
INSERT INTO sample_date_validation (date_column, is_abnormal, comments) VALUES | |||
('2024-01-15', FALSE, 'Valid date'), | |||
('2023-12-31', FALSE, 'Valid date'), | |||
('2000-02-29', FALSE, 'Valid leap year date'), | |||
('1999-01-01', FALSE, 'Valid date'), | |||
('2024-13-01', TRUE, 'Invalid month (13)'), | |||
('2023-00-10', TRUE, 'Invalid month (00)'), | |||
('2023-06-31', TRUE, 'Invalid day (June has 30 days)'), | |||
('2023-02-30', TRUE, 'Invalid day (February)'), | |||
('1900-00-00', TRUE, 'Zero-like date'), | |||
('0000-00-00', TRUE, 'All zeros'), | |||
('abcd-ef-gh', TRUE, 'Non-numeric string'), | |||
('20240115', TRUE, 'Missing dashes'), | |||
('15-01-2024', TRUE, 'Wrong format DD-MM-YYYY'), | |||
('2024/01/15', TRUE, 'Wrong separator'), | |||
(NULL, TRUE, 'NULL value'); | |||
</pre> | |||
Query SQL: | |||
<pre> | |||
SELECT CAST(date_column AS DATE) AS date_column | |||
FROM my_table | |||
WHERE CAST(date_column AS DATE) IS NULL | |||
AND date_column IS NOT NULL; | |||
</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> | <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 12: | Line 103: | ||
</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 21: | Line 112: | ||
</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 30: | Line 122: | ||
</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 37: | Line 130: | ||
STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S') IS NULL; | 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> | </pre> | ||
| Line 64: | Line 167: | ||
[[Category:Data Science]] | [[Category:Data Science]] | ||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category: | [[Category:String manipulation]] | ||