SQLite Julianday Function

The SQLite julianday function facilitates you to apply modifiers and then returns the date as a Julian Day after taking the date as an input.

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.

Syntax:

snippet
julianday(timestring [, modifier1, modifier2, ... modifier_n ] )

Example1: Retrieve current date:

snippet
SELECT julianday('2017-04-13');
SELECT julianday('2017-04-13 16:45');
SELECT julianday('2017-04-13 16:45:30');
SELECT julianday('now');

Output:

SQLite Julianday function 1 SQLite Julianday function 2 SQLite Julianday function 3 SQLite Julianday function 4

Example2: Retrieve First Day of the Month:

snippet
SELECT julianday('2017-04-13', 'start of month');
SELECT julianday('now', 'start of month');
SELECT julianday('2017-03-13', '-6 days');
SELECT julianday('now', '-6 days');

Output:

SQLite Julianday function 5 SQLite Julianday function 6 SQLite Julianday function 7 SQLite Julianday function 8

Example2: Retrieve Last Day of the Month:

snippet
SELECT julianday('2017-03-07', 'start of month', '+1 month', '-1 day');
SELECT julianday('now', 'start of month', '+1 month', '-1 day');
SELECT julianday('2017-03-07', '+24 days');
SELECT julianday('now', '+24 days');

Output:

SQLite Julianday function 9 SQLite Julianday function 10 SQLite Julianday function 11 SQLite Julianday function 12

Example3: Add/ subtract years/ days on the current date:

snippet
SELECT julianday('2017-04-14', '+2 years');
SELECT julianday('now', '+5 years');
SELECT julianday('now', '-7 days');

Output:

SQLite Julianday function 13 SQLite Julianday function 14 SQLite Julianday function 15
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents +