Ravindra BagaleCourses & study guides Track your progress

9. Date and Time Queries for Analysts

9.3 How to Compute Date Differences (Cheque Clearing Delay Story)

By Last updated:

Why. Customers complain when a cheque takes too long to clear. The bank needs the number of days between two dates.

What. julianday(date) turns a date into a day number. Subtracting two julianday values gives the number of days between them. date(value, '+3 days') moves a date forward, which is useful for due dates.

How. For each cheque, subtract the posting date from the clearing date, and also show a promised date three days after posting.

Steps

  1. Run the first example to see days_to_clear for every cheque.
  2. Notice that a cheque with no clearing date shows NULL, because it has not cleared yet.
  3. Run the second example to see the promised date three days after posting.
  4. Use date(posted_at) inside julianday() so that the time of day does not add part of a day.

Examples

Example 1 · dataset: northstar_bank

SELECT txn_id,
       date(posted_at) AS posted_date,
       cleared_on,
       julianday(cleared_on) - julianday(date(posted_at)) AS days_to_clear
FROM transactions
WHERE txn_type = 'cheque'
ORDER BY txn_id;

Output

txn_idposted_datecleared_ondays_to_clear
22026-08-192026-08-212.0
42026-08-282026-09-025.0
62026-08-302026-09-012.0
92026-09-142026-09-184.0
132026-09-252026-09-294.0
152026-09-29
182026-10-202026-10-233.0
202026-10-27
242026-10-312026-11-022.0

Meaning. Most cheques clear in 2 to 5 days. Two cheques show NULL because they have not cleared yet.

Example 2 · dataset: northstar_bank

SELECT txn_id,
       date(posted_at)            AS posted_date,
       date(posted_at, '+3 days') AS promised_by,
       cleared_on
FROM transactions
WHERE txn_type = 'cheque'
  AND cleared_on IS NOT NULL
ORDER BY txn_id;

Output

txn_idposted_datepromised_bycleared_on
22026-08-192026-08-222026-08-21
42026-08-282026-08-312026-09-02
62026-08-302026-09-022026-09-01
92026-09-142026-09-172026-09-18
132026-09-252026-09-282026-09-29
182026-10-202026-10-232026-10-23
242026-10-312026-11-032026-11-02

Meaning. promised_by is three days after posting. Any row where cleared_on is later than promised_by missed the promise.

Ravindra Bagale's Tip

julianday() can return a decimal when times are included. Wrap date-time text in date() when you only want whole days.