Command guide

intermediate date-time MySQL MariaDB SQL Server PostgreSQL SQLite

Add Or Subtract Days From A Date

Calculate a future or past date by adding or subtracting an interval from an existing date.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Calculate a 7-day follow-up date for each event

Each event_date is shifted 7 days forward. March 1 becomes March 8; March 15 becomes March 22. To subtract days, use a negative value (INTERVAL -7 DAY in MySQL/MariaDB, DATEADD(day, -7, ...) in SQL Server, - INTERVAL '7 days' in PostgreSQL).

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_date VARCHAR(10));

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Kickoff', '2024-03-01'),
  (2, 'Review', '2024-03-15');

Validated query

SQL

SELECT
  name,
  event_date,
  DATE_ADD (event_date, INTERVAL 7 DAY) AS followup_date
FROM
  events
ORDER BY
  id;

Expected result

name	event_date	followup_date
Kickoff	2024-03-01	2024-03-08
Review	2024-03-15	2024-03-22

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_date VARCHAR(10));

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Kickoff', '2024-03-01'),
  (2, 'Review', '2024-03-15');

Validated query

SQL

SELECT
  name,
  event_date,
  CAST(
    DATEADD (DAY, 7, CAST(event_date AS DATE)) AS VARCHAR(10)
  ) AS followup_date
FROM
  events
ORDER BY
  id;

Expected result

name	event_date	followup_date
Kickoff	2024-03-01	2024-03-08
Review	2024-03-15	2024-03-22

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_date VARCHAR(10));

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Kickoff', '2024-03-01'),
  (2, 'Review', '2024-03-15');

Validated query

SQL

SELECT name, event_date, (event_date::DATE + INTERVAL '7 days')::DATE::TEXT AS followup_date FROM events ORDER BY id;

Expected result

name	event_date	followup_date
Kickoff	2024-03-01	2024-03-08
Review	2024-03-15	2024-03-22

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_date VARCHAR(10));

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Kickoff', '2024-03-01'),
  (2, 'Review', '2024-03-15');

Validated query

SQL

SELECT
  name,
  event_date,
  date(event_date, '+7 days') AS followup_date
FROM
  events
ORDER BY
  id;

Expected result

name	event_date	followup_date
Kickoff	2024-03-01	2024-03-08
Review	2024-03-15	2024-03-22

Each engine uses a different function for date arithmetic. The results are identical.

Useful when

Where this command helps.

calculating a deadline or expiry date from a start date
adding or subtracting a fixed number of days from each row

Explanation

What the command is doing.

Each engine has its own function for date arithmetic. MySQL and MariaDB use DATE_ADD(date, INTERVAL n DAY). SQL Server uses DATEADD(day, n, date). PostgreSQL uses the + operator with an INTERVAL literal. All engines support other units: MONTH, YEAR, HOUR, etc. Using negative values subtracts instead of adding.

Add Or Subtract Days From A Date

Calculate a 7-day follow-up date for each event

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.