Math with Dates

SQL Server trung cấp

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!

SQL Server trung cấp