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().
SELECT JULIANDAY('2025-03-01') - JULIANDAY('2025-01-01'); -- 59.0
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 |
-- 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
INTEGERto drop the fraction, orROUND()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.25is 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.
Related
DATE(),STRFTIME(),DATEDIFF(), date modifiers,ROUND()- Lessons: Date functions, Numeric functions