Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Dealing with dates
Working with time data
Date and time date
Extracting dates and times
Doing arithmetic with dates
Converting date and time data
36. The FORMAT() function
Building date and time data from parts
Summary and review

Instruction

Very good. We can also use the FORMAT() function to change how dates and times are formatted. It looks like this:

FORMAT(value, format[, culture])

The first argument is the value that will be formatted, such as a date. The second is a string specifying the format. We'll explain more about that in a moment. The third argument, culture, is optional and refers to a local language or format preference.

This function will return a string—or, if there's a problem, NULL. Let's look at an example:

SELECT
  Date,
  FORMAT(Date,'D','de-de') AS GermanLong,
  FORMAT(Date,'d','de-de') AS GermanShort
FROM Flight
WHERE Id = 1;

The default date format is '2016-04-03'. However, we've selected the culture option 'de-de', which means Germany; the 'D' format displays the German preference as 'Sonntag, 3. April 2016', and the 'd' format displays another format, '03.04.2016'.

Each country or language has a preferred way to display a date. Further, each country usually also has a long format (e.g., Sunday, 3 April 2016) and a short format (e.g., 03.04.16). When the format argument is uppercase ('D'), the long format is shown. When it is lowercase ('d'), the abbreviated format is shown. Of course, there are many other formats. You can even write in the format you prefer instead of using a letter code. For example:

SELECT
  Date,
  FORMAT(Date, 'dd/MM/yy') AS NewDateTime
FROM Flight
WHERE Id = 1;

In this format, the 'dd/MM/yy' date of flights will display '03/04/16'. In this case, we don't need the culture parameter.

The formatting uses a .NET framework format string. You can find more information about formats in .NET documentation.

Exercise

The manager needs a report about aircraft production dates. It will be sent to people in the USA, Great Britain, and Germany. Show the production dates in these formats: 'en-US', 'de-de', and 'en-gb'. Use the 'D' pattern to format the date. Additionally show the unchanged original column. Name the formatted columns: DateUSA, DateGermany, and DateGB, respectively.

Stuck? Here's a hint!

Use FORMAT() with the column name, the pattern 'D', and the appropriate name of the culture.