Math with Dates

Intermediate SQL Server

Ginger Grant

Instructor

DATEPART

DATEPART is used to determine what part of the date you want to calculate. Some of the common abbreviations are:

DD for Day
MM for Month
YY for Year
HH for Hour

Common date functions in T-SQL

DATEADD(): Add or subtract datetime values
- Always returns a date
DATEDIFF(): Obtain the difference between two datetime values
- Always returns a number

DATEADD

To Add or subtract a value to get a new date use DATEADD()

DATEADD (DATEPART, number, date)

DATEPART: Unit of measurement (DD, MM etc.)
number: An integer value to add
date: A datetime value

Date math with DATEADD (I)

What date is 30 days from June 21, 2020?

SELECT DATEADD(DD, 30, '2020-06-21')
+-----------------------------+ 
|(No Column Name)             | 
|-----------------------------+ 
|2020-07-21 00:00             | 
+-----------------------------+

Date math with DATEADD (II)

What date is 30 days before June 21, 2020?

SELECT DATEADD(DD, -30, '2020-06-21')
+-----------------------------+ 
|(No Column Name)             | 
|-----------------------------+ 
|2020-05-22 00:00             | 
+-----------------------------+

DATEDIFF

Returns a date after a number has been added or subtracted to a date

DATEDIFF (datepart, startdate, enddate)

datepart: Unit of measurement (DD, MM etc.)
startdate: The starting date value
enddate: An ending datetime value

Date math with DATEDIFF

SELECT DATEDIFF(DD, '2020-05-22', '2020-06-21') AS Difference1,
       DATEDIFF(DD, '2020-07-21', '2020-06-21') AS Difference2

+--------------------+--------------------+ 
|Difference1         |Difference2         |
+--------------------+--------------------+
|30                  |-30                 |
+--------------------+--------------------+

Let's practice!

Intermediate SQL Server