JULIANDAY

Working with dates

SQLite in your browser
Lesson concept What you'll do
1 / 4

Count the days between two dates, or group loans by month.

JULIANDAY turns a date into a day number; STRFTIME picks out parts like the month.

The database
11 rows
loan_idbook_idmember_idloan_datereturned
111022026-01-022026-01-22
231012026-02-102026-02-17
3101062026-03-052026-03-21
481012026-04-072026-04-28
561022026-05-202026-06-03
641042026-06-222026-06-29
741062026-08-022026-08-20
871042026-08-112026-09-01
921042026-09-09NULL
1051052026-09-15NULL
1131062026-10-28NULL
Click a name to type it
Your query
Hint: click the green edit button on the board, or type it yourself
Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How JULIANDAY, STRFTIME works

  • How does SQLite store dates?

    As text in the form 2026-03-08. Year first means they sort, compare and filter correctly: WHERE loan_date >= '2026-03-01' works.

  • How do I count days between dates?

    julianday(returned) - julianday(loan_date)

    julianday turns a date into a day number, like an Excel date serial, so subtracting gives days.

  • How do I pick out the year or month?

    strftime('%Y', loan_date) gives the year, '%m' the month and '%Y-%m' both. date('now') is today, and date(loan_date, '+21 days') adds days.

  • Is this the same in every database?

    No, dates are where databases differ most. The tables below show the main ones.

  • How is this like Excel?

    julianday is like Excel's date serials (=C2-B2 for days), and strftime is like =TEXT(B2, "yyyy-mm").

Other databases

How other databases do it

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Days between two dates
DatabaseHowNote
SQLite (these lessons)julianday(returned) - julianday(loan_date)SQLite has no date type: dates are text, and these functions read them.
PostgreSQLreturned - loan_dateWith real date columns, subtracting gives days.
MySQL / MariaDBDATEDIFF(returned, loan_date)
SQL ServerDATEDIFF(day, loan_date, returned)The earlier date comes first.
Oraclereturned - loan_dateWith DATE columns, subtracting gives days.
Sybase ASEDATEDIFF(day, loan_date, returned)
The month of a date
DatabaseHowNote
SQLite (these lessons)strftime('%m', loan_date)Gives text like '03'.
PostgreSQLEXTRACT(MONTH FROM loan_date)TO_CHAR(loan_date, 'YYYY-MM') for 2026-03.
MySQL / MariaDBMONTH(loan_date)DATE_FORMAT(loan_date, '%Y-%m') for 2026-03.
SQL ServerMONTH(loan_date)FORMAT(loan_date, 'yyyy-MM') for 2026-03.
OracleEXTRACT(MONTH FROM loan_date)TO_CHAR(loan_date, 'YYYY-MM') for 2026-03.
Sybase ASEMONTH(loan_date)Or DATEPART(month, loan_date).

Know someone fighting with a spreadsheet? Send them this page.