Date Functions in SQL (SQLite Edition)

SQLite includes powerful functions to work with dates and times.

Common Date Functions in SQLite

• DATE('now') — Returns the current date.
• julianday(date) — Converts a date into a numeric value representing days since a fixed point in time.
• strftime(format, date) — Extracts parts of a date like year ('%Y'), month ('%m'), or day ('%d').

Example: Find Students Who Joined Over 30 Days Ago

SELECT * FROM students
WHERE 
julianday('now') - julianday(join_date) > 30;

This finds students whose join_date was more than 30 days ago.

Date functions help analyse timelines, calculate durations, filter date ranges, and more — even without DATEDIFF or DATEADD which exist in other SQL dialects like SQL Server.

‍