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
23. Using YEAR(), MONTH(), and DAY() with aggregate functions
Doing arithmetic with dates
Converting date and time data
Building date and time data from parts
Summary and review

Instruction

You can use these functions with GROUP BY or HAVING. This is especially useful when preparing reports. The query below can be used to generate a report about the number of aircraft produced each month:

SELECT
  YEAR(ProducedDate) AS Year,
  MONTH(ProducedDate) AS Month,
  COUNT(Id) AS FlightsNumber
FROM Aircraft
GROUP BY YEAR(ProducedDate),
  MONTH(ProducedDate);

As you can see, rows are grouped by the extracted YEAR and MONTH values.

Exercise

The manager needs a report of the average delay, in minutes, of all flights for each year after 2014. Write the query for this report. Name the columns Year and AvgDelay.