Validate the datetime value

Validate the value should be well-formated value e.g. year-month-day 2026-05-06

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: 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 date_column is a valid date e.g. YYYY-MM-DD

Schema of column date_column

• Type: varchar or date
• Expected format: YYYY-MM-DD

Schema SQL:

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');

Query SQL:

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;

Verify the year of value is not 1900 or 1999 years

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.

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

Verify the value of DAY() 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

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

e.g. 14:49: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

e.g. 2026-05-06 14:49: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

Not recommended!

SELECT `my_date_column`
FROM `my_table`
WHERE `my_date_column` NOT REGEXP '^[0-9]{4}';

PHP appracoh

Using PHP strtotime function

Excel approach

1. Validate the total length of cell value is equal to 10 e.g. =IF(LEN(B2)<>10, TRUE, FALSE)
2. Validate the positions of the - symbols
3. 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)
 )