In SQL Server, the FORMAT function is a powerful tool that allows you to format dates and other values according to specific styles and cultures. It provides a flexible way to display data in a desired format without the need for complex conversions or calculations.

Let’s take a look at some examples to understand how the FORMAT function works.

Formatting Dates

One common use case for the FORMAT function is to format dates in different styles. For example, if we have a date value of January 5th, 2013, we can choose to display it as either 1/5/2013 or 5/1/2013, depending on our preference or the culture of the computer running the query.

Using the CONVERT function, we can achieve this by specifying the desired style code. For example:

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
CONVERT(NVARCHAR, HireDate, 101) AS CharDate
FROM Employee

This query will return the hire date in the format ‘MM/DD/YYYY’ (e.g., 01/05/2013).

However, with the introduction of the FORMAT function in SQL Server 2012, we can achieve the same result in a more straightforward way. Instead of using CONVERT, we can simply use the FORMAT function and specify the desired format within single quotes. For example:

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
FORMAT(HireDate, 'd') AS CharDate
FROM Employee

This query will return the hire date in the same format as before, ‘MM/DD/YYYY’.

Formatting with Cultures

The FORMAT function also allows us to specify the culture for formatting dates. By default, the function uses the culture set on the server. However, we can override this by specifying a different culture code as the third parameter of the FORMAT function.

For example, if we want to display the hire date in the format ‘DD/MM/YYYY’ (e.g., 16/03/1996) as customary in Great Britain, we can use the following query:

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
FORMAT(HireDate, 'd', 'en-gb') AS CharDate
FROM Employee

This query will return the hire date in the desired format for the specified culture.

Formatting Months

The FORMAT function can also be used to format months in different ways. For example, we can display the month as a full name, a three-letter abbreviation, or a two-digit numeric value.

Here are some examples:

SELECT HireDate,
FORMAT(HireDate, 'MMMM', 'en-us') AS CharDate
FROM Employee

This query will return the hire date with the month fully spelled out (e.g., January, August, September).

SELECT HireDate,
FORMAT(HireDate, 'MMM', 'en-us') AS CharDate
FROM Employee

This query will return the hire date with a three-letter month abbreviation (e.g., Jan, Aug, Sep).

SELECT HireDate,
FORMAT(HireDate, 'MM', 'en-us') AS CharDate
FROM Employee

This query will return the hire date with a two-digit numeric month (e.g., 01, 08, 09).

It’s important to note that the case of the format code matters. For example, ‘M’ will return the whole month spelled out, while ‘m’ will return the minutes if used with a datetime value.

Conclusion

The FORMAT function in SQL Server provides a convenient way to format dates and other values according to specific styles and cultures. It allows you to customize the display of data without the need for complex conversions or calculations. By understanding the different format codes and culture options, you can easily format your data to meet your requirements.

For more in-depth information on the FORMAT function and other SQL Server topics, you can refer to the book “SQL Queries 2012 Joes 2 Pros Volume 4” by Joes2Pros.