Date functions in SQL

Problem

Until when can I return it?

A customer writes: “Until when can I return my order?” The shop gives 30 days from the order date. Today is the 25th of October 2026.

Counting days on a calendar is slow, and it is easy to get wrong at the end of a month. Let the database do it.

The idea

A date plus days

In the shop, dates are text in the form year-month-day: 2026-10-25. This is the international standard (ISO 8601). That is why dates have sorted and compared correctly so far.

The function date(date, change) does arithmetic with dates:

SQLdatabase: shop

SELECT id, ordered_at, date(ordered_at, '+30 days') AS return_until
FROM orders
WHERE status = 'delivered'
ORDER BY ordered_at DESC
LIMIT 5;

Order 54 can be returned until today, inclusive. Changes can be days, months and years, forwards or backwards, and 'start of month' gives the first day of the month:

SQLdatabase: shop

SELECT date('2026-10-25', '-7 days'),
       date('2026-10-25', '+1 month'),
       date('2026-10-25', 'start of month');

More precisely, if you are curious

These functions are strict about the form of the date. Without the leading zero (2026-9-30), date gives NULL, and comparing as text lies: '2026-10-25' comes before '2026-9-30', because “1” comes before “9”. And '+1 month' from the 31st of January makes the 31st of February, that is the 3rd of March:

SQLdatabase: shop

SELECT date('2026-01-31', '+1 month'), date('2026-9-30'), '2026-10-25' > '2026-9-30';

So always store dates with leading zeros, and take care when adding months to the last days of a month.

Try it

The payment deadline

New orders (new) must be paid within 3 days of ordering. Show the number, the date and the deadline in a column called pay_until.

SQLdatabase: shop

SELECT id, ordered_at FROM orders WHERE status = 'new';

Solution

SELECT id, ordered_at, date(ordered_at, '+3 days') AS pay_until FROM orders WHERE status = 'new';

The idea

Year, month, day

strftime(format, date) takes out a part of the date: '%Y' is the year, '%m' the month, '%d' the day, '%w' the day of the week (0 is Sunday, 6 is Saturday).

SQLdatabase: shop

SELECT name, joined_at,
       strftime('%Y', joined_at) AS year,
       strftime('%m', joined_at) AS month
FROM customers
WHERE city = 'Varna';

The result is text, with a leading zero: the month is 03, not 3.

See it

Signed up in March

Marketing wants the customers who signed up in March, in any year. Run:

SQLdatabase: shop

SELECT name, joined_at FROM customers
WHERE strftime('%m', joined_at) = 3;

1. Why does WHERE strftime('%m', joined_at) = 3 find nobody?Because strftime returns the text '03', and text is not equal to the number 3Because nobody signed up in MarchBecause %m means minutes

With the same form, text with a leading zero, two come out:

SQLdatabase: shop

SELECT name, joined_at FROM customers
WHERE strftime('%m', joined_at) = '03';

The idea

How many days between two dates

julianday(date) turns the date into a number: how many days have passed since a very old starting date. You do not need the number itself, but the difference of two such numbers is the number of days between the dates.

How many days the kitchen products have been in the shop:

SQLdatabase: shop

SELECT name, added_at,
       julianday('2026-10-25') - julianday(added_at) AS days
FROM products
WHERE category_id = 8;

More precisely, if you are curious

In a real report, instead of '2026-10-25' you will write date('now') or CURRENT_DATE: today's date. In SQLite both are in UTC, not local time, so near midnight your local date and the database's can differ by a day while the database's is not. The lessons use a fixed date so that the result is the same every day.

If the date has a time as well (2026-10-25 18:20:00), datetime and time work the same way as date, and julianday gives part of a day too: 12 hours are 0.5.

Try it

Paid and waiting

Paid orders (paid) have not been shipped yet. How many days has each been waiting by the 25th of October 2026? Show the number, the date and the days in a column called waiting, the longest waiting first.

SQLdatabase: shop

SELECT id, ordered_at FROM orders WHERE status = 'paid';

Solution

SELECT id, ordered_at, julianday('2026-10-25') - julianday(ordered_at) AS waiting
FROM orders
WHERE status = 'paid'
ORDER BY waiting DESC;

Exercise

Two tasks

1. The courier does not work on Sundays. Which orders were placed on a Sunday? Show the number and the date.

The orders placed on a Sunday:

SQLdatabase: shop

Solution

SELECT id, ordered_at FROM orders WHERE strftime('%w', ordered_at) = '0';

2. Which delivered orders can still be returned today, the 25th of October 2026? Show the number, the date and the return deadline in a column called return_until.

The delivered orders that can still be returned:

SQLdatabase: shop

Solution

SELECT id, ordered_at, date(ordered_at, '+30 days') AS return_until
FROM orders
WHERE status = 'delivered' AND date(ordered_at, '+30 days') >= '2026-10-25';

Test

Check yourself

1. What does date('2026-10-25', '+7 days') give?2026-10-182026-11-012026-10-32

2. How many days does julianday('2026-10-25') - julianday('2026-10-01') give?0.242524

3. Which condition finds the customers who signed up in 2025?joined_at = '2025'strftime('%Y', joined_at) = '2025'strftime('%Y', joined_at) = 2025

4. Why do the lessons write '2026-10-25' and not date('now')?No reasonBecause with the current date the result is different every day, and the task check would have nothing to compare withBecause date('now') does not work in SQLite

Date functions

Cookies