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

Most finance work is shaped by the calendar. Month-end closes. Quarterly reports. Fiscal-year shifts when your company doesn’t run on a January-to-December clock. Aged AP reports that bucket invoices by how late they are. Day-of-week patterns that reveal the business calendar hiding inside your data.

In Episode 6 we used window functions to compute month-over-month change and running totals. The window mechanics did the heavy lifting. But every one of those queries had strftime('%Y-%m', posting_date) AS month somewhere near the top — and that little expression is doing a lot more work than it looks.

This episode is about that part. Date functions, calendar arithmetic, fiscal-year math, and the canonical AP aging report. All on real AtlasParts data. We are deliberately keeping report-bundling for Episode 8 — today is just the date-mechanics layer.

Why dates are awkward in SQL

A date in a database is just a string of characters most of the time — '2025-04-15' — and any “date function” is really a string parser plus a calendar engine. SQLite stores dates as ISO-8601 text, which is why strftime (steal-the-format-from-time) works the way it does: feed it a format code and a date string, and it returns a piece of the date.

Knowing what those format codes do is the whole game.

A finance analyst rarely needs more. The trick is composing them.

SELECT strftime('%Y-%m', posting_date) AS month
FROM   gl_journal
LIMIT 5;

That gives you 2025-01, 2025-01, 2025-02, etc. — which is what you GROUP BY for any monthly report. Same data, different format code, gives you 2025 (yearly), 2025-Q1 (manual quarter), or 2025-W15 (weekly).

The hidden calendar in the data

Here is a small query that tells you a surprising amount about how AtlasParts operates:

SELECT strftime('%w', order_date) AS dow,
       COUNT(*)                   AS orders,
       ROUND(SUM(CAST(order_total AS REAL)), 0) AS revenue
FROM   sales_orders
GROUP  BY dow
ORDER  BY dow;

Notice what is missing: 0 (Sunday) and 6 (Saturday). AtlasParts does not place a single order on a weekend. That is not a SQL trick — it is the business calendar embedded in the data. Whoever generated the orders only wrote them on weekdays.

A second query confirms it: SELECT COUNT(DISTINCT order_date) FROM sales_orders returns 261. There are exactly 261 weekdays in 2025. Every single working day has at least one order. None of the 104 weekend days do.

That kind of fingerprint shows up in real financial data too. Postings cluster around month-end. Bank reconciliations only happen on banking days. Payroll runs land on specific cycles. strftime('%w', ...) is one of the cheapest ways to find these patterns and ask whether they make sense.

Quarter math without a quarter function

SQLite does not have a QUARTER() function. Postgres and SQL Server do; SQLite makes you compute it. The expression is short and worth memorising:

'Q' || ((CAST(strftime('%m', order_date) AS INT) - 1) / 3 + 1)

Read inside-out: extract the month as '04', cast to integer 4, subtract one 3, integer-divide by three 1, add one 2, prepend 'Q' → 'Q2'. Five operations to land on the right quarter.

Apply it across the AtlasParts year:

SELECT 'Q' || ((CAST(strftime('%m', order_date) AS INT) - 1) / 3 + 1) AS qtr,
       COUNT(*),
       ROUND(SUM(CAST(order_total AS REAL)), 0) AS revenue
FROM   sales_orders
GROUP  BY qtr
ORDER  BY qtr;

Q4 is 2.5× larger than Q1 — by both order count and revenue. That seasonality is the kind of thing a CFO needs to know before walking into a board meeting.

Date arithmetic: the date() function

SQLite’s date() function takes a date and a list of modifiers and gives back a new date. This is how you do calendar arithmetic without leaving the SELECT clause.

SELECT date('2025-04-15', '+30 days');     -- 2025-05-15
SELECT date('2025-04-15', 'start of month'); -- 2025-04-01
SELECT date('2025-04-15', 'start of month', '+1 month', '-1 day'); -- 2025-04-30 (end of month)
SELECT date('now', 'start of year', '+1 year', '-1 day'); -- last day of current year

The composability is the magic. start of month snaps to the first of the month; +1 month, -1 day walks forward and back to land on the last day. You can chain as many modifiers as you want, in order. This is how you build month-end report dates, fiscal-year boundaries, or “30 days from invoice” payment due dates.

julianday() for day differences

When you want how many days between two dates, strftime cannot help — string subtraction is meaningless. Use julianday():

SELECT julianday('2025-12-31') - julianday('2025-01-01'); -- 364.0

Julian day numbers are continuous numeric values where every full day is exactly 1.0. The difference is the day count.

In AtlasParts, every AP invoice has both an invoice_date and a due_date. The difference is the payment terms:

SELECT CAST(julianday(due_date) - julianday(invoice_date) AS INT) AS terms,
       COUNT(*) AS invoices
FROM   ap_invoices
GROUP  BY terms
ORDER  BY terms;

Three clean cohorts: net 30, net 45, net 60. Exactly what you would expect from a vendor-management system that lets each supplier negotiate their own terms. If a fourth row appeared with an oddball number — terms=37 or something — that is a data-entry error worth investigating.

Fiscal years that are not calendar years

Most companies do not run on a January-December fiscal year. Retailers often start in February. The US federal government uses October-September. UK companies frequently use April-March. The math is the same in every case: shift the month, then year accordingly.

Suppose AtlasParts switched to an April-March fiscal year. April 2025 would be the first month of FY2026 (because the fiscal year is named for the ending calendar year). The shift is two parts:

SELECT order_date,
       CASE WHEN CAST(strftime('%m', order_date) AS INT) >= 4
            THEN CAST(strftime('%Y', order_date) AS INT) + 1
            ELSE CAST(strftime('%Y', order_date) AS INT)
       END AS fiscal_year,
       ((CAST(strftime('%m', order_date) AS INT) - 4 + 12) % 12) + 1 AS fiscal_period
FROM   sales_orders
LIMIT 6;

The + 12) % 12 trick handles the wrap: April becomes period 1, March becomes period 12, and the modulo arithmetic does the math without a CASE expression. Memorise it once and you can rewrite it for any fiscal-year start: change the 4 to your start month and you’re done.

Why bother? Because every report a CFO sees is fiscal, not calendar. “Q1 results” means the first three months of the fiscal year, not Jan-Feb-Mar. Doing this shift in SQL — not in Excel after the fact — is what lets you wire dashboards directly to the data warehouse.

The aged AP report

Now we put it all together. The AP aging report is one of the most important in finance: it tells you how many of your unpaid bills have crossed each lateness threshold. Standard buckets are Current (not yet due), 1-30 days overdue, 31-60, 61-90, and 90+.

The pattern is julianday(report_date) - julianday(due_date) for each open invoice, then a CASE to bucket it:

SELECT CASE WHEN julianday('2025-12-31') - julianday(due_date) < 0  THEN 'Current'
            WHEN julianday('2025-12-31') - julianday(due_date) <= 30 THEN '1-30 days'
            WHEN julianday('2025-12-31') - julianday(due_date) <= 60 THEN '31-60 days'
            WHEN julianday('2025-12-31') - julianday(due_date) <= 90 THEN '61-90 days'
            ELSE '90+ days'
       END AS aging_bucket,
       COUNT(*) AS invoices,
       ROUND(SUM(CAST(amount AS REAL)), 0) AS amount
FROM   ap_invoices
WHERE  status = 'Open'
GROUP  BY aging_bucket
ORDER  BY MIN(julianday('2025-12-31') - julianday(due_date));

That last row is the alarm. 22 invoices, $317K, more than 90 days past due. Either AtlasParts is sitting on cash they intend to dispute, or a vendor manager has a backlog, or the data is wrong. All three are worth a meeting.

The ORDER BY MIN(julianday(...)) trick at the bottom is what keeps the buckets in the right order without a manual CASE in the ORDER BY — it sorts by the smallest day-difference each bucket contains, which lines up Current-to-90+ correctly.

You can pivot this report any number of ways. By vendor: GROUP BY vendor_id, bucket. By currency. By cost center. By month the invoice was issued. The bucket logic stays the same.

What to take away

If you remember three things from this episode:

1. strftime extracts; date() computes; julianday() measures. Those three functions cover almost every date task you will face in finance reporting. Composing them well is most of the skill.
2. Fiscal-year math is just a shift. A CASE for the year and a modular arithmetic expression for the period — and your reports automatically respect whatever calendar your company runs on.
3. The aged AP report is the canonical finance date pattern. julianday(report_date) - julianday(due_date) plus a CASE for buckets. Once you can build it, you can build accounts receivable aging, contract renewals coming due, parts inventory by age, and a hundred other “how old is this thing” reports.

Episode 8 is the capstone — we will assemble a small reusable query library: P&L by month, balance-sheet rollup, AR/AP aging, and a cash-flow waterfall. All built on top of the windowing (Ep6) and date mechanics (this episode) you now have.

The calendar will stop being something you fight in Excel, and start being a tool you compose in SQL.