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
mNo edit summary |
|||
| (One intermediate revision 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 | ||
| Line 15: | Line 15: | ||
FROM `my_table` | FROM `my_table` | ||
WHERE `my_date_column` LIKE '1900-%'; | 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> | </pre> | ||