Dates and Reporting Patterns: strftime, Fiscal Years, and AP Aging | SQL for Finance Ep7

AtlasParts dataset and atlasparts.db: https://github.com/GoCelesteAI/excel-powerbi-for-finance Source code: https://github.com/GoCelesteAI/sql-for-finance Episode 7 of SQL for Finance. Date functions, calendar arithmetic, fiscal-year math, and the canonical aged AP report — the date-mechanics layer that powers every finance report. Most finance work is shaped by the calendar. Month-end closes, quarterly reports, fiscal-year shifts when your company doesn't run on January through December, aged invoice reports that bucket bills by how late they are. Episode 6's window functions did the heavy lifting, but every one of those queries had strftime('%Y-%m', posting_date) somewhere near the top — and that little expression is doing a lot more work than it looks. This episode is about that part. What You'll Learn: - The strftime cheatsheet — %Y, %m, %d, %w, %j, %W and how to compose them for any report shape - The hidden weekday calendar in AtlasParts — strftime('%w') reveals zero weekend orders, 261 distinct order days = exactly 2025 weekdays - Quarter math without a QUARTER() function — the (month - 1) / 3 + 1 expression - The date() function's modifier composability — start of month, +1 month, -1 day to land on month-end - julianday() for day differences — payment terms, days overdue - Fiscal-year math as a shift — CASE for the year, modular arithmetic for the period - The aged AP report — julianday + CASE buckets, the canonical date pattern in finance - The ORDER BY MIN(...) trick that keeps buckets in order without a manual CASE Timestamps: 0:00 - Intro - dates and reporting patterns 0:40 - strftime anatomy - format codes and composability 1:50 - The hidden weekday calendar - strftime('%w') reveal 3:45 - Calendar shifts - quarter and fiscal-year math 5:15 - julianday demo - payment terms cohorts 6:50 - The aged AP centerpiece - bucket logic + ORDER BY MIN trick 9:50 - Recap - three takeaways 11:00 - End screen Key Takeaways: 1. Three functions cover almost every date task in finance reporting. strftime extracts pieces of a date as strings — year, month, day-of-week, week-of-year. date() computes new dates with composable modifiers — +30 days, start of month, +1 month, -1 day. julianday() measures day differences as numbers — subtract two julianday values and you get the day count between them. Compose them well and you have most of the toolkit. 2. The format codes for strftime are worth memorising. %Y for year, %m for month, %d for day, %w for day-of-week (0=Sun), %j for day-of-year, %W for week-of-year. The composability matters more than the codes themselves: same date string, different format codes give you '2025', '2025-04', '2025-04-15', 'Q2', or '15' — without a single new column. 3. SQLite has no QUARTER() function, so you build it: 'Q' || ((CAST(strftime('%m', d) AS INT) - 1) / 3 + 1). Five operations to land on the right quarter. Memorise it once. The same pattern (modular arithmetic on the month) is how you handle fiscal-year shifts: (month - fiscal_start + 12) % 12 + 1 gives you the fiscal period, where the + 12) % 12 handles the wrap from December back to January. 4. julianday() turns string subtraction (which is meaningless) into day-count subtraction (which is exactly what aging reports need). julianday(due_date) - julianday(invoice_date) reveals AtlasParts' three clean payment-term cohorts: net 30 (209 invoices), net 45 (111), net 60 (100). Same expression with a fixed '2025-12-31' report date gives you days overdue for an aging report. 5. The aged AP report is the canonical finance date pattern. julianday(report_date) - julianday(due_date) plus a CASE for buckets — Current, 1-30, 31-60, 61-90, 90+. Apply to AtlasParts open invoices and you find the alarm: 22 invoices, $318K, more than 90 days past due. The ORDER BY MIN(julianday(...)) trick at the bottom keeps the buckets in the right order without writing a manual CASE in the ORDER BY. Once you can build aging, you can build AR aging, contract renewals, parts inventory by age — a hundred other "how old is this thing" reports. #SQL #SQLite #FinanceAnalytics #DataAnalytics #SQLforFinance #strftime #julianday #FiscalYear #APAging #FinancialReporting #ERPdata #Database #LearnSQL --- Generated by GoCelesteAI · part of the SQL for Finance series