Month-End Billing Check — Solution & ExplanationProblems / Month-End Billing Check / EditorialApproach

Compute month-end via next month. DATE_TRUNC('month', d) pins the month's start; + INTERVAL 1 MONTH lands on the next month's start (interval arithmetic knows month lengths); - INTERVAL 1 DAY steps back to the true last day — 28, 29, 30, or 31 as the calendar demands.

Derive both outputs from one gap. DATE_DIFF('day', billing_date, month_end) is the countdown; the flag is that countdown hitting zero. One CTE, no duplicate logic.

Key Insight"Last day of month" is the textbook example of a boundary best computed by overshooting into the next period and stepping back — the calendar's irregularity (variable month lengths, leap years) is absorbed entirely by the engine's + 1 MONTH arithmetic instead of your code. Engines do offer LAST_DAY(), but it returns a DATE and quietly changes your column's type mid-pipeline; the trunc-and-interval idiom stays in timestamp-land and works letter-for-letter across DuckDB, Spark, and most warehouses.Hints

You can't add '31 days' or hardcode month lengths — February exists. Go through the NEXT month instead: truncate to this month's first day, add one month, subtract one day.

That idiom is leap-year-proof for free: DATE_TRUNC('month', 2024-02-15) + 1 month - 1 day = 2024-02-29, and the same expression on 2023 gives the 28th.

days_to_month_end is a plain day difference to the computed month_end; is_month_end is just 'that difference is zero' — derive the flag from the number rather than re-deriving the date twice.

Watch the leap trap in the data: 2024-02-28 is NOT month-end (the 29th is), but 2023-02-28 IS.

Solve Month-End Billing Check yourself →