What are SQL Dates?

Dates are used to store information such as registration dates, order dates, delivery dates, birthdays, and event dates. SQLite does not have a dedicated DATE data type. Instead, dates are commonly stored as TEXT, REAL, or INTEGER.

For most simple applications, the ISO-8601 text format YYYY-MM-DD is convenient and works well with SQLite’s built-in date functions.

Basic Date Example

A date can be stored as text using the YYYY-MM-DD format.

DROP TABLE IF EXISTS events;

CREATE TABLE events (
  event_id INTEGER PRIMARY KEY,
  event_name TEXT NOT NULL,
  event_date TEXT NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
  ('Tech Workshop', '2026-09-10'),
  ('Database Seminar', '2026-10-05'),
  ('Science Exhibition', '2026-11-18');

SELECT *
FROM events
ORDER BY event_date;

SQLite Current Date

SQLite provides the date('now') function to return the current date in UTC using the YYYY-MM-DD format.

SELECT date('now') AS current_date;

Current Date and Time

Use datetime('now') when you need both the current date and time.

SELECT datetime('now') AS current_datetime;

Extracting Year, Month, and Day

SQLite’s strftime() function can extract individual parts of a date. The format codes %Y, %m, and %d represent the year, month, and day.

SELECT
  strftime('%Y', '2026-08-27') AS year,
  strftime('%m', '2026-08-27') AS month,
  strftime('%d', '2026-08-27') AS day;

Adding Days to a Date

Use the date() function with a modifier such as '+7 days' to calculate a future date.

SELECT
  date('2026-08-27', '+7 days') AS date_after_7_days;

Subtracting Days from a Date

A negative date modifier can be used to calculate an earlier date.

SELECT
  date('2026-08-27', '-10 days') AS date_10_days_earlier;

Adding Months to a Date

SQLite also supports month modifiers for date calculations.

SELECT
  date('2026-08-27', '+3 months') AS date_after_3_months;

Comparing Dates

ISO-formatted dates can be compared using normal comparison operators such as >, <, and =.

DROP TABLE IF EXISTS deliveries;

CREATE TABLE deliveries (
  delivery_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  delivery_date TEXT NOT NULL
);

INSERT INTO deliveries (customer_name, delivery_date)
VALUES
  ('Aarav', '2026-08-20'),
  ('Meera', '2026-09-02'),
  ('Kabir', '2026-08-28'),
  ('Nisha', '2026-09-15');

SELECT
  customer_name,
  delivery_date
FROM deliveries
WHERE delivery_date > '2026-08-27'
ORDER BY delivery_date;

Finding Dates Between Two Dates

The BETWEEN operator can be used to select dates within a specified range. The range is inclusive.

DROP TABLE IF EXISTS appointments;

CREATE TABLE appointments (
  appointment_id INTEGER PRIMARY KEY,
  patient_name TEXT NOT NULL,
  appointment_date TEXT NOT NULL
);

INSERT INTO appointments (patient_name, appointment_date)
VALUES
  ('Aditi', '2026-08-10'),
  ('Rohan', '2026-08-18'),
  ('Sana', '2026-08-25'),
  ('Karan', '2026-09-03');

SELECT
  patient_name,
  appointment_date
FROM appointments
WHERE appointment_date BETWEEN '2026-08-15' AND '2026-08-31'
ORDER BY appointment_date;

Formatting Dates with strftime()

The strftime() function can format a date into a different display format.

SELECT
  strftime('%d-%m-%Y', '2026-08-27') AS formatted_date;

Finding the Day of the Week

The %w format in strftime() returns the weekday number, where Sunday is 0 and Saturday is 6.

SELECT
  strftime('%w', '2026-08-27') AS weekday_number;

Finding the Start of a Month

The start of month modifier returns the first day of the month containing the specified date.

SELECT
  date('2026-08-27', 'start of month') AS first_day_of_month;

Finding the Start of the Year

The start of year modifier returns January 1 of the year containing the specified date.

SELECT
  date('2026-08-27', 'start of year') AS first_day_of_year;

Calculating the Difference Between Dates

SQLite’s julianday() function can be used to calculate the number of days between two dates.

SELECT
  julianday('2026-09-15') - julianday('2026-09-01')
  AS days_difference;

Dates in a Table

Date functions can also be used directly with date columns stored in a table.

DROP TABLE IF EXISTS subscriptions;

CREATE TABLE subscriptions (
  subscription_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  start_date TEXT NOT NULL
);

INSERT INTO subscriptions (customer_name, start_date)
VALUES
  ('Isha', '2026-07-05'),
  ('Dev', '2026-08-12'),
  ('Nisha', '2026-09-20');

SELECT
  customer_name,
  start_date,
  date(start_date, '+30 days') AS renewal_date
FROM subscriptions
ORDER BY start_date;

Common SQLite Date Functions

Common SQLite Date Modifiers

Advantages of SQL Dates

• Store and organize calendar dates efficiently.
• Make it easy to compare dates.
• Support date calculations such as adding or subtracting days.
• Allow filtering records within date ranges.
• Provide built-in functions for formatting and date calculations.

Best Practices

• Use the YYYY-MM-DD format when storing dates as TEXT in SQLite.
• Keep date values consistent throughout a table.
• Use SQLite date functions such as date(), datetime(), and strftime() for date operations.
• Use julianday() when calculating differences between dates.
• Remember that SQLite’s 'now' date/time value is based on UTC.
• Do not assume SQLite has a dedicated DATE storage type like some other database systems.