Command guide

intermediate date-time MySQL MariaDB SQL Server PostgreSQL SQLite

Calculate The Number Of Days Between Two Dates

Find how many days separate two date values using each engine's date difference function.

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

Docker-validated Not currently validation-green

Example 1

Calculate the number of days each project ran

Alpha ran from January 5 to January 20, a span of 15 days. Beta ran from February 1 to February 28, a span of 27 days. Note that MySQL and SQL Server share the function name but differ in argument order: MySQL puts the end date first, SQL Server puts the unit first and the start date second.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE projects (
  id INT,
  name VARCHAR(50),
  start_date VARCHAR(10),
  end_date VARCHAR(10)
);

INSERT INTO
  projects (id, name, start_date, end_date)
VALUES
  (1, 'Alpha', '2024-01-05', '2024-01-20'),
  (2, 'Beta', '2024-02-01', '2024-02-28');

Validated query

SQL

SELECT
  name,
  DATEDIFF (end_date, start_date) AS days
FROM
  projects
ORDER BY
  id;

Expected result

name	days
Alpha	15
Beta	27

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE projects (
  id INT,
  name VARCHAR(50),
  start_date VARCHAR(10),
  end_date VARCHAR(10)
);

INSERT INTO
  projects (id, name, start_date, end_date)
VALUES
  (1, 'Alpha', '2024-01-05', '2024-01-20'),
  (2, 'Beta', '2024-02-01', '2024-02-28');

Validated query

SQL

SELECT
  name,
  DATEDIFF (DAY, start_date, end_date) AS days
FROM
  projects
ORDER BY
  id;

Expected result

name	days
Alpha	15
Beta	27

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE projects (
  id INT,
  name VARCHAR(50),
  start_date VARCHAR(10),
  end_date VARCHAR(10)
);

INSERT INTO
  projects (id, name, start_date, end_date)
VALUES
  (1, 'Alpha', '2024-01-05', '2024-01-20'),
  (2, 'Beta', '2024-02-01', '2024-02-28');

Validated query

SQL

SELECT name, end_date::DATE - start_date::DATE AS days FROM projects ORDER BY id;

Expected result

name	days
Alpha	15
Beta	27

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE projects (
  id INT,
  name VARCHAR(50),
  start_date VARCHAR(10),
  end_date VARCHAR(10)
);

INSERT INTO
  projects (id, name, start_date, end_date)
VALUES
  (1, 'Alpha', '2024-01-05', '2024-01-20'),
  (2, 'Beta', '2024-02-01', '2024-02-28');

Validated query

SQL

SELECT
  name,
  CAST(
    JULIANDAY (end_date) - JULIANDAY (start_date) AS INT
  ) AS days
FROM
  projects
ORDER BY
  id;

Expected result

name	days
Alpha	15
Beta	27

MySQL/MariaDB use DATEDIFF(end, start). SQL Server uses DATEDIFF(day, start, end) — note reversed date argument order. PostgreSQL subtracts DATE values directly. SQLite uses JULIANDAY subtraction.

Useful when

Where this command helps.

computing the duration of a project or task in days
calculating how many days have elapsed since a reference date

Explanation

What the command is doing.

Each engine uses a different function to compute the number of days between two dates. MySQL and MariaDB use DATEDIFF(end, start) with the later date first. SQL Server also uses DATEDIFF but with the unit as the first argument: DATEDIFF(day, start, end). PostgreSQL subtracts two DATE values directly with the - operator. SQLite uses JULIANDAY(end) - JULIANDAY(start) cast to an integer.

Calculate The Number Of Days Between Two Dates

Calculate the number of days each project ran

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.