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
24. Using the DATEPART() function
Doing arithmetic with dates
Converting date and time data
Building date and time data from parts
Summary and review

Instruction

Well done! You can also use the DATEPART function to extract parts of date and time data. The function looks like this:

SELECT
  DATEPART(datepart, date)

The date argument is the field, string, or expression that you'll extract data from. The datepart argument specifies what information will be returned. With this one function, you can return all types of time and date parts, including year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, and tzoffset (time zone offset).

Many T-SQL functions use datepart specifiers as an argument, so it's good to get familiar with this concept.

The DATEPART function allows you to get part of a date or time. It returns an integer. Let's try it out in a query:

SELECT
  DATEPART(hour, ArrivalTime) AS Hour
FROM Route;

This will return only the hour of each flight's arrival time. For route PA2342, the arrival time is 09:30:00. In this case, DATEPART returns the hour, 9.

Note that the datepart (hour) isn't written between single quotes. The reason is simple: it's an identifier. We can't put identifiers between single quotes.

Exercise

For each route, show its code and the departure time in the following, changed format: hh.mm, where hh is the hour and mm the minutes. Name the column Time.

Later in this course, you'll learn functions that help you display date and time in a format of your choice. But in this exercise, we'll do it "by hand."

Stuck? Here's a hint!

You will have to use the DATEPART() function twice and concatenate it appropriately. Don't forget about the dot (N'.') in between. Remember that DATEPART returns an integer, so you can't use + concatenation.