Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Date and Time Functions

SQL JULIANDAY() function

Reference for SQLite JULIANDAY(): convert dates to day numbers, subtract two dates to get days, hours or minutes between them, and what other databases use instead.

JULIANDAY(value, modifier, ...) converts a date-time into a Julian day number: the count of days (with a fraction for the time of day) since noon on 24 November 4714 BC in the proleptic Gregorian calendar. The number itself is rarely useful. What matters is that dates become ordinary numbers, so subtracting two of them gives the number of days between them.

That is the standard way to calculate ages, durations and "days since" in SQLite, which has no DATEDIFF().

SQL
SELECT JULIANDAY('2025-03-01') - JULIANDAY('2025-01-01');   -- 59.0

Try it

SQL Years of service from hire dates ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Day numbers, fractions and units

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
JULIANDAY(time_value [, modifier, ...])
Name Description
time_value A date or date-time (ISO text, 'now', a number).
modifier Optional date modifiers.

Returns: a floating-point Julian day number such as 2460676.5 for midnight on 2025-01-01, or NULL if the value is invalid or NULL. A day starts at noon in the Julian day system, so midnight ends in .5.

Dialect differences

Database How to get the difference between two dates
SQLite JULIANDAY(b) - JULIANDAY(a) (days, fractional); UNIXEPOCH(b) - UNIXEPOCH(a) (seconds)
PostgreSQL date_b - date_a gives integer days; timestamp_b - timestamp_a gives an interval; EXTRACT(EPOCH FROM ...) for seconds
MySQL DATEDIFF(b, a) days; TIMESTAMPDIFF(HOUR, a, b) for other units
SQL Server DATEDIFF(day, a, b) counts day boundaries crossed
Oracle date_b - date_a is a number of days, fractions included
SQL
-- PostgreSQL
SELECT DATE '2025-03-01' - DATE '2025-01-01';      -- 59

-- MySQL
SELECT DATEDIFF('2025-03-01', '2025-01-01');       -- 59

-- SQL Server
SELECT DATEDIFF(day, '2025-01-01', '2025-03-01');  -- 59

Usage notes

  • The result is a real number. Cast to INTEGER to drop the fraction, or ROUND() it. Casting truncates toward zero, so a 1.9-day gap becomes 1.
  • Argument order matters: later date minus earlier date gives a positive result.
  • Whole days between dates ignoring the time of day: JULIANDAY(DATE(b)) - JULIANDAY(DATE(a)).
  • Age in years is not simply days divided by 365; leap years add a fraction. / 365.25 is a decent approximation; exact birthdays need a comparison of month and day.
  • Hours and minutes: multiply the difference by 24 or 1440.
  • Precision. A double holds about a millisecond of precision at modern day numbers, so comparing differences to exact values may need ROUND().
  • Index use. Wrapping a column in JULIANDAY() stops an index on that column from helping; compare the raw column to a computed boundary instead.