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 to YYYY-MM-DD, require exactly eight digits, and compare the candidate to SQLite date(candidate,+0 days) so normalization exposes impossible dates. Invalid or missing values remain NULL; keep the source row. SQL Server uses TRY_CONVERT with an explicit style.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

WITH shaped AS(
  SELECT
    row_id,
    raw_date,
    SUBSTR(raw_date,1,4)||'-'||SUBSTR(raw_date,5,2)||'-'||SUBSTR(raw_date,7,2) AS candidate
  FROM raw_dates
)
SELECT
  row_id,
  raw_date,
  CASE
    WHEN raw_date GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
      AND date(candidate,'+0 days')=candidate
      THEN candidate
    ELSE NULL
  END AS clean_date
FROM shaped

Watch for: SQLite may normalize February 30 to March instead of rejecting it. NULL after CAST is not a portable guarantee.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Understand the source contract. Fictional extracts, load counts, invalid dates, versioned dimensions, refresh staging and a data catalog. The foundation project is latest-state; separate history and incremental labs demonstrate extensions.

   step-1.sql Run

   sql›

   SELECT *
   FROM raw_dates;

2. 2

   Execute the transformation. Eight digits do not prove a valid date. Shape YYYYMMDD to YYYY-MM-DD, require exactly eight digits, and compare the candidate to SQLite date(candidate,+0 days) so normalization exposes impossible dates. Invalid or missing values remain NULL; keep the source row. SQL Server uses TRY_CONVERT with an explicit style.

   step-2.sql Run

   sql›

   WITH shaped AS(
     SELECT
       row_id,
       raw_date,
       SUBSTR(raw_date,1,4)||'-'||SUBSTR(raw_date,5,2)||'-'||SUBSTR(raw_date,7,2) AS candidate
     FROM raw_dates
   )
   SELECT
     row_id,
     raw_date,
     CASE
       WHEN raw_date GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
         AND date(candidate,'+0 days')=candidate
         THEN candidate
       ELSE NULL
     END AS clean_date
   FROM shaped

3. 3

   Test another question. SQLite may normalize February 30 to March instead of rejecting it. NULL after CAST is not a portable guarantee.

   step-3.sql Run

   sql›

   WITH checked AS(
     WITH shaped AS(
       SELECT
         row_id,
         raw_date,
         SUBSTR(raw_date,1,4)||'-'||SUBSTR(raw_date,5,2)||'-'||SUBSTR(raw_date,7,2) AS candidate
       FROM raw_dates
     )
     SELECT
       row_id,
       raw_date,
       CASE
         WHEN raw_date GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
           AND date(candidate,'+0 days')=candidate
           THEN candidate
         ELSE NULL
       END AS clean_date
     FROM shaped
   )
   SELECT row_id,raw_date
   FROM checked
   WHERE clean_date IS NULL