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
| (11 intermediate revisions by the same user not shown) | |||
| Line 5: | Line 5: | ||
== MySQL approach == | == MySQL approach == | ||
=== Verify the value is | === Verify the column value is a valid (non-zero) datetime e.g. 0000-00-00 00:00:00 === | ||
Schema of column value | Schema of column value | ||
* Type: datetime | * 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 | * 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> | <pre> | ||
| Line 15: | Line 70: | ||
FROM `my_table` | FROM `my_table` | ||
WHERE `my_date_column` LIKE '1900-%'; | 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> | </pre> | ||
| Line 22: | Line 86: | ||
Schema of column value | Schema of column value | ||
* Type: datetime | * Type: {{kbd | key=datetime}} or {{kbd | key=VARCHAR}} | ||
* Default value: allow be NULL | * Default value: allow be NULL | ||