Performing arithmetic operations on dates

Functions for Manipulating Data in SQL Server

Ana Voicu

Data Engineer

Types of operations with dates

Operations using arithmetic operators ( +, -)
Modify the value of a date - DATEADD()
Return the difference between two dates -DATEDIFF()

Arithmetic operations

DECLARE @date1 datetime = '2019-01-01';
DECLARE @date2 datetime = '2020-01-01';

SELECT 
    @date2 + 1 AS add_one,
    @date2 - 1 AS subtract_one,
    @date2 + @date1 AS add_dates,
    @date2 - @date1 AS subtract_date;

| add_one                 | subtract_one            | add_dates               | subtract_date           |
|-------------------------|-------------------------|-------------------------|-------------------------|
| 2020-01-02 00:00:00.000 | 2019-12-31 00:00:00.000 | 2139-01-01 00:00:00.000 | 1901-01-01 00:00:00.000 |

DATEADD(datepart, number, date)

Add or subtract a number of time units from a date

SELECT 
    first_name,
    birthdate,
    DATEADD(YEAR, 5, birthdate) AS fifth_birthday,
    DATEADD(YEAR, -5, birthdate) AS subtract_5years,
    DATEADD(DAY, 30, birthdate) AS add_30days,
    DATEADD(DAY, -30, birthdate) AS subtract_30days
FROM voters;

| first_name | birthdate  | fifth_birthday | subtract_5years | add_30days | subtract_30days |
|------------|------------|----------------|-----------------|------------|-----------------|
| Carol      | 1989-01-15 | 1994-01-15     | 1984-01-15      | 1989-02-14 | 1988-12-16      |
| Dennis     | 1972-03-11 | 1977-03-11     | 1967-03-11      | 1972-04-10 | 1972-02-10      |

DATEDIFF(datepart, startdate, enddate)

Returns the number of units between two dates

SELECT 
    first_name,
    birthdate,
    first_vote_date,
    DATEDIFF(YEAR, birthdate, first_vote_date) AS age_years,
    DATEDIFF(QUARTER, birthdate, first_vote_date) AS age_quarters,
    DATEDIFF(DAY, birthdate, first_vote_date) AS age_days,
    DATEDIFF(HOUR, birthdate, first_vote_date) AS age_hours
  FROM voters;

| first_name | birthdate  | first_vote_date | age_years | age_quarters | age_days | age_hours |
|------------|------------|-----------------|-----------|--------------|----------|-----------|
| Carol      | 1989-01-15 | 2015-03-09      | 26        | 104          | 9549     | 229176    |
| Dennis     | 1972-03-11 | 2013-10-29      | 41        | 167          | 15207    | 364968    |

Let's practice!

Functions for Manipulating Data in SQL Server