Introduction
Dealing with dates
Working with time data
Date and time date
Extracting dates and times
22. The YEAR(), MONTH(), and DAY() functions
Doing arithmetic with dates
Converting date and time data
Current date and time data
Building date and time data from parts
Summary and review

Instruction

Great! Now, let's see how we can manipulate date and time data. SQL Server provides functions that allow us to extract parts of dates and/or times. The simplest of them are:

  • YEAR()—extracts the year from a string or a field and returns it as an integer; if the year value is zero or if there is only time data in that field, the function returns the base year, which is 1900.
  • MONTH()—works like YEAR(), except it returns the number of the month as an integer. If the month value is zero or the field contains only time data, the function returns the base month (1). Note: January is denoted by a 1, February by 2, and so on.
  • DAY()—works like YEAR(), but returns the day as an integer. If the day value is zero or contains only time data, the function returns the base day (1). Note: DAY() returns the day of the month (i.e., 1 for the first of the month).

Here's how these functions work:

SELECT
  YEAR(Date) AS Year,
  MONTH(Date) AS Month,
  DAY(Date) AS Day
FROM Flight;

This query extracts the day, month, and year of flights using the data in the Date column.

Exercise

Extract the year and month when each aircraft was launched. Use data from the LaunchedDatetime column, and name the new columns LaunchedDateMonth and LaunchedDateYear. Sort in ascending order by year and month.