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

Instruction

Nice. Now we'll find the difference between two moments in time. SQL Server offers the useful DATEDIFF() function for this, and it looks like:

DATEDIFF(datepart, start, stop)

This function returns an integer that is the difference between the start and end (stop) dates. The datepart argument specifies the time period (minute, hour, week, year, etc). The returned value is based on the unit given in datepart. You can use DATEDIFF() to find time differences as well as date differences.

The query below returns the number of weeks between March 14, 2017 and March 30, 2017:

SELECT
  DATEDIFF(week, '2017-03-14', '2017-03-30');

This returns a 2, and there are indeed two whole weeks between those dates. However, the actual difference is 16 days, which is more than two weeks. Since we specified weeks, though, we get a two.

What if we reverse the start and stop dates?

SELECT
  DATEDIFF(week, '2017-03-30', '2017-03-14');

In this case, the function returns a -2. The negative signifies that the end date is before the start date.

Exercise

For each route that has a distance under 1000km, find out how many minutes it takes. Show each route's code and time in minutes. Name the last column FlightTime.