SQLDatabase.NET

JulianDay SQL Function

The JulianDay() function takes a date, allows to apply modifiers and returns the date as a Julian Day. A Julian Day is the number of days since Nov 24, 4714 BC 12:00pm Greenwich time in the Gregorian calendar. The julianday function returns the date as a floating point number.

The syntax for the julianday function is JulianDay(timestring , [ modifier1, modifier2, ... modifier_n ] )

A date value can be one of the following:

timestring Explanation
now now is a literal used to return the current date
YYYY-MM-DD Date value formatted as 'YYYY-MM-DD'
YYYY-MM-DD HH:MM Date value formatted as 'YYYY-MM-DD HH:MM'
YYYY-MM-DD HH:MM:SS Date value formatted as 'YYYY-MM-DD HH:MM:SS'
YYYY-MM-DD HH:MM:SS.SSS Date value formatted as 'YYYY-MM-DD HH:MM:SS.SSS'
HH:MM Date value formatted as 'HH:MM'
HH:MM:SS Date value formatted as 'HH:MM:SS'
HH:MM:SS.SSS Date value formatted as 'HH:MM:SS.SSS'
YYYY-MM-DDTHH:MM Date value formatted as 'YYYY-MM-DDTHH:MM' where T is a literal character separating the date and time portions
YYYY-MM-DDTHH:MM:SS Date value formatted as 'YYYY-MM-DDTHH:MM:SS' where T is a literal character separating the date and time portions
YYYY-MM-DDTHH:MM:SS.SSS Date value formatted as 'YYYY-MM-DDTHH:MM:SS.SSS' where T is a literal character separating the date and time portions
DDDDDDDDDD Julian date number


modifier1, modifier2, ... modifier_n are optional. These are modifiers that are applied to the timestring. Each modifier is applied in order and are cumulative. They can be one or more of the following:
Modifier Explanation
[+-]NNN years Number of years added/subtracted to the date
[+-]NNN months Number of months added/subtracted to the date
[+-]NNN days Number of days added/subtracted to the date
[+-]NNN hours Number of hours added/subtracted to the date
[+-]NNN minutes Number of minutes added/subtracted to the date
[+-]NNN seconds Number of seconds added/subtracted to the date
[+-]NNN.NNNN seconds Number of seconds (and fractional seconds) added/subtracted to the date
start of year Shifting the date back to the start of the year
start of month Shifting the date back to the start of the month
start of day Shifting the date back to the start of the day
weekday N Moves the date forward to the next date where weekday number is N
(0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday)
unixepoch Used with the DDDDDDDDDD timestring to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01)
localtime Adjusts date to localtime, assuming the timestring was expressed in UTC
utc Adjusts date to utc, assuming the timestring was expressed in localtime 


		/* Following will output 2457969.54695416 */ 

        SELECT julianday('now');
		


		/* Following will output 2457966.5 */ 

        SELECT JulianDay('Now', 'Start of Month');
		


		/* Following will output 2457813.5 */ 

        SELECT JulianDay('2017-03-07', '-6 days');
		


		/* Following will output 2457843.5 */ 

        SELECT JulianDay('2017-03-07', 'Start Of Month', '+1 Month', '-1 Day');	
		


		/* Following will output 2457454.5 */ 

        SELECT JulianDay('2014-03-07', '+2 years');
		


		/* Following will output 2457826.5 */ 

        SELECT JulianDay('2017-03-07', '+7 days');

JulianDay function can add subtract date value using days to years.