Validate the datetime value: Difference between revisions
Jump to navigation
Jump to search
mNo edit summary |
|||
(8 intermediate revisions by the same user not shown) | |||
Line 8: | Line 8: | ||
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) | * Default value: 0000-00-00 00:00:00 (NOT allow be NULL)、"1999/12/31 上午 12:00:00" on Google sheet | ||
<pre> | <pre> | ||
Line 17: | Line 17: | ||
</pre> | </pre> | ||
=== Verify the value is not 1900 | === Verify the year of value is not 1900 or 1999 years === | ||
Schema of column value | Schema of column value | ||
* Type: | * 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 27: | Line 29: | ||
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 34: | Line 45: | ||
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 | ||
Latest revision as of 15:38, 17 September 2023
Validate the value should be well-formated value e.g. year-month-day 2024-04-29
MySQL approach[edit]
Verify the value is not 0000-00-00 00:00:00[edit]
Schema of column value
- Type: datetime
- Default value: 0000-00-00 00:00:00 (NOT allow be NULL)、"1999/12/31 上午 12:00:00" on Google sheet
SELECT `my_date_column` FROM `my_table` WHERE `my_date_column` LIKE '1900-%';
Verify the year of value is not 1900 or 1999 years[edit]
Schema of column value
- Type: VARCHAR
- Default value: allow be NULL
Abnormal values e.g. 1900-01-08 00:00:00 because the Excel datetime issue[1].
SELECT `my_date_column` FROM `my_table` WHERE `my_date_column` LIKE '1900-%';
Or list the year list
SELECT DISTINCT LEFT(`my_date_column`, 4) FROM `my_table` ORDER BY LEFT(`my_date_column`, 4) LIMIT 50
Verify the value is null[edit]
Verify the value of DAY()[2] is NULL
Schema of column value
- Type: datetime or VARCHAR
- Default value: allow be NULL
SELECT `my_date_column` FROM `my_table` WHERE DAY(`my_date_column`) IS NULL;
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;
Verify the value should be year/month/day format[edit]
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;
Verify the value should be hour:minute:second format[edit]
e.g. 14:18:06
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;
Verify the value should be year-month-day hour:minute:second format[edit]
e.g. 2024-04-29 14:18:06
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;
Verify the value not start with four number, which means year[edit]
Not recommended!
SELECT `my_date_column` FROM `my_table` WHERE `my_date_column` NOT REGEXP '^[0-9]{4}';
PHP appracoh[edit]
Using PHP strtotime function
Excel approach[edit]
- Validate the total length of cell value is equal to 10 e.g. =IF(LEN(B2)<>10, TRUE, FALSE)
- Validate the positions of the - symbols
- Combined the above condition, the verify formula is: (If the cell value is well-formatted, the formula returns true)
=NOT( IF(OR( ISERROR(FIND("-", B2, 1)), ISERROR(FIND("-", B2, 6)), IF(LEN(B2)<>10, TRUE, FALSE) ), TRUE, FALSE) )