Validate the datetime value: Difference between revisions

Jump to navigation Jump to search

Validate the datetime value (edit)

Revision as of 12:29, 6 May 2026

3,406 bytes added ,  12:29
→‎Verify the date_column is a valid date e.g. YYYY-MM-DD
mNo edit summary
 
(20 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>


== PHP appracoh ==
== PHP appracoh ==
Using PHP [https://www.php.net/manual/en/function.strtotime.php strtotime] function
Using PHP [https://www.php.net/manual/en/function.strtotime.php strtotime] function
== Excel approach ==
# Validate the total length of cell value is equal to 10 e.g. {{kbd | key=<nowiki>=IF(LEN(B2)<>10, TRUE, FALSE)</nowiki>}}
# Validate the positions of the {{kbd | key=<nowiki>-</nowiki>}} symbols
# Combined the above condition, the verify formula is: (If the cell value is well-formatted, the formula returns true)
<pre>
=NOT(
    IF(OR(
          ISERROR(FIND("-", B2, 1)),
          ISERROR(FIND("-", B2, 6)),
          IF(LEN(B2)<>10, TRUE, FALSE)
          ), TRUE, FALSE)
)
</pre>


== References ==
== References ==
Line 47: Line 164:
[[Category:Spreadsheet]]  
[[Category:Spreadsheet]]  
<!-- [[Category:Excel]] -->
<!-- [[Category:Excel]] -->
[[Category:Data_hygiene]]
[[Category:Data Validation]]
[[Category:Data Science]]
[[Category:Data Science]]
[[Category:MySQL]]  
[[Category:MySQL]]  
[[Category:Text file processing]]
[[Category:String manipulation]]
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/21958...26335"

Navigation menu