Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in MS SQL Server

Introduction
Dealing with dates
Working with time data
Date and time date
Extracting dates and times
Doing arithmetic with dates
28. The DATEADD() function
Converting date and time data
Building date and time data from parts
Summary and review

Instruction

Okay, let's try operating on date and time data. We'll start with the relatively simple DATEADD() function, which looks like this:

DATEADD(datepart, number, date)

DATEADD() takes the following arguments:

  • datepart—the part of the date that will be changed. Some common values that the datepart argument can take are: year, quarter, month, day, week, hour, minute, and second.
  • number—the number that will be added to the given date.
  • date—the date being changed. This can be an actual date or a column value.

Let's suppose someone entered the wrong date for a flight and we would like our query to return a date 2 months later. We'd write:

SELECT
  DATEADD(month, 2, LaunchedDatetime)
FROM Aircraft;

In this query, two months are added to the aircraft launch date, and a new launch date is returned (two months are added). Note that the datepart argument (month) is given as an identifier, without any quotes.

Exercise

PerfectAir decided to use the discontinued aircraft with Id = 5 once again. Show its ID, original discontinued date, and the discontinuation date postponed by 11 months. Name the column PostponedDiscontinuationDatetime.