lesson 80 Warehouse & reporting projects · advanced

Validate calendar dates before converting

Validate real calendar dates before converting text.

the idea

Eight digits do not prove a valid date. Shape YYYYMMDD into YYYY-MM-DD, require exactly eight digits, and keep the candidate only if date(candidate, '+0 days') returns the same text; SQLite normalizes impossible dates such as 2011-11-31 to another day, which exposes them. Invalid values become NULL while the source row is kept.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   See invalid dates normalize away. Run this. SQLite rolls impossible dates like 2011-11-31 forward—comparing normalized output to your candidate is how you reject bad calendar values.the pattern

   example.sql Run

   sql›

   SELECT
     date('2011-11-31', '+0 days') AS normalized,
     date('2011-02-29', '+0 days') AS not_a_leap_year;

2. 2

   Group raw date string lengths. Most export values are eight characters; shorter or longer strings fail the format gate before you parse them.

   step-2.sql Run

   sql›

   SELECT raw_date, length(raw_date) AS chars, COUNT(*) AS rows
   FROM raw_dates
   GROUP BY length(raw_date)
   ORDER BY rows DESC;

3. 3

   Test eight-digit shape with GLOB. GLOB confirms YYYYMMDD shape before you build candidate and run the calendar check.

   step-3.sql Run

   sql›

   SELECT
     raw_date,
     raw_date GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' AS eight_digits
   FROM raw_dates
   LIMIT 10;

common mistake

date('2011-11-31') returns '2011-12-01' rather than NULL; comparing the result with the candidate is what rejects it.