SQLite JULIANDAY() Function

The SQLite JULIANDAY() function takes a date and applies modifiers on it and then returns the date as a Julian Day.

A Julian Day is the number of days since November 24, 4714 BC 12:00 P.M Greenwich time in the Gregorian calendar. This function returns the date as a floating point number.

Syntax

JULIANDAY(time-value, modifier, modifier, ...)

Parameters

Return Value

Returns the date as a Julian Day.

Example: Simple Date

The SQLite JULIANDAY() function can be used to convert a simple date to a Julian Day.

SELECT JULIANDAY('2018-08-18');
Result: '2458348.5'

SELECT JULIANDAY('2018-08-18 08:23:19');
Result: '2458348.84952546'

Example: Current Date and time

The SQLite 'now' time-value can be used to get the current date and time.

SELECT JULIANDAY('now');
Result: '2459679.01231368'

Example: First day of the month

To get the first day of the month, 'start of month' modifier can be used. See the example below:

SELECT JULIANDAY('2018-08-18 08:23:19', 'start of month');
Result: '2458331.5'

SELECT JULIANDAY('now', 'start of month');
Result: '2459670.5'

Example: Last day of the month

To get the last day of the month, first, the 'start of month' modifier is used to calculate the start day of the month and then 1 month is added and then 1 day is subtracted.

SELECT JULIANDAY('2018-08-18', 'start of month', '+1 month', '-1 day');
Result: '2458361.5'

SELECT JULIANDAY('now', 'start of month', '+1 month', '-1 day');
Result: '2459699.5'

Example: Adding/Subtracting years

To add/subtract years from a time-value, [+-]NNN years modifier can be used. In the example below 2 years is added and subtracted from a given time-value.

SELECT JULIANDAY('2018-08-18 08:23:19', '+2 years');
Result: '2459079.84952546'

SELECT JULIANDAY('2018-08-18 08:23:19', '-2 years');
Result: '2457618.84952546'

Example: Adding/Subtracting days

To add/subtract days drom a time-value, [+-]NNN days modifier can be used. In the example below 10 days is added and subtracted from a given time-value.

SELECT JULIANDAY('2018-08-18 08:23:19', '+10 days');
Result: '2458358.84952546'

SELECT JULIANDAY('2018-08-18 08:23:19', '-10 days');
Result: '2458338.84952546'

Example: Adding/Subtracting hours

To add/subtract hours from a time-value, [+-]NNN hours modifier can be used. In the example below 5 hours is added and subtracted from a given time-value.

SELECT JULIANDAY('2018-08-18 08:23:19', '+5 hours');
Result: '2458349.0578588'

SELECT JULIANDAY('2018-08-18 08:23:19', '-5 hours');
Result: '2458348.64119213'

Example: Adding/Subtracting minutes

To add/subtract minutes from a time-value, [+-]NNN minutes modifier can be used. In the example below 30 minutes is added and subtracted from a given time-value.

SELECT JULIANDAY('2018-08-18 08:23:19', '+30 minutes');
Result: '2458348.8703588'

SELECT JULIANDAY('2018-08-18 08:23:19', '-30 minutes');
Result: '2458348.82869213'