In Microsoft SQL Server, formatting dates has always been a challenge. The CONVERT function, used in earlier versions of SQL Server, had limited flexibility and required knowledge of format numbers. However, starting with SQL Server 2012, a new function called FORMAT was introduced, which made date formatting much easier.

The FORMAT function allows you to specify the display format you want for your dates, without the need to know format numbers. This tutorial will provide you with various examples of how to use the FORMAT function to format dates in SQL Server.

Using the FORMAT Function

To use the FORMAT function, you need to provide two parameters: the value you want to format (such as a date column or a variable) and the desired format string. Here’s the syntax:

FORMAT(value, format[, culture])

Let’s look at some examples:

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS FormattedDate;

The above query will return the current date in the format “dd/MM/yyyy”, such as “21/03/2021”.

SELECT FORMAT(GETDATE(), 'hh:mm:ss') AS FormattedTime;

This query will return the current time in the format “hh:mm:ss”, such as “02:48:42”.

Common Date Format Options

The FORMAT function provides various options to format dates. Here are some commonly used options:

• dd: day of the month from 01 to 31
• MM: month number from 01 to 12
• yyyy: year with four digits
• hh: hour from 01 to 12
• HH: hour from 00 to 23
• mm: minute from 00 to 59
• ss: second from 00 to 59
• tt: AM or PM

These options can be combined to create different date formats. For example:

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;

The above query will return the current date in the format “yyyy-MM-dd”, such as “2021-03-21”.

Formatting Dates with Culture

The FORMAT function also allows you to specify a culture parameter to obtain regional formatting. For example:

SELECT FORMAT(GETDATE(), 'd', 'en-us') AS FormattedDate;

In the USA, the date format is month, day, year. The above query will return the current date in the format “M/d/yyyy”, such as “3/21/2021”.

SELECT FORMAT(GETDATE(), 'd', 'es-bo') AS FormattedDate;

In Bolivia, the date format is day, month, year. The above query will return the current date in the format “d/M/yyyy”, such as “21/3/2021”.

Conclusion

In this article, we explored the FORMAT function in SQL Server, which provides a more flexible and user-friendly way to format dates. By using the FORMAT function, you can easily achieve the desired date format without the need to remember format numbers. However, it’s important to note that the FORMAT function may have performance implications compared to other approaches like the CONVERT function or CAST function.

Remember to consider the specific requirements of your application and choose the appropriate method for formatting dates in SQL Server.

Article Last Updated: 2023-10-18