Format SQL Server Dates with FORMAT Function

By: Daniel Calbimonte   |   Updated: 2023-10-18   |   Comments (18)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Dates

Problem

Microsoft SQL Server 2008 and earlier versions used the CONVERT functions to handle date formatting in SQL queries, SELECT statements, stored procedures and T-SQL scripts. In this tip, Date and Time Conversions Using SQL Server, we have a list of the available examples on how to use the CONVERT function to handle different date formats in a SQL database.

As you may know, the CONVERT function is not very flexible and we have limited date formats. In Microsoft SQL Server 2012 and later, the function FORMAT has been introduced which is much easier to use to format dates.  This tutorial shows different examples of using this new function to format dates.

Solution

Starting with SQL Server 2012, a function to handle formatting dates was introduced which is similar to Oracle's to_date function. Many Oracle DBAs complained about the SQL Server CONVERT function and its poor flexibility and now we have a new way to format dates in SQL Server.

With the SQL Server FORMAT function we do not need to know the format number to use to get the right date format we want, we can just specify the display format we want and we get that format.

SQL Date Format with the FORMAT function

1. Use the FORMAT function to format the date and time data types from a date column (date, datetime, datetime2, smalldatetime, datetimeoffset, etc. data type) in a table or a variable such as GETDATE()
2. To get DD/MM/YYYY use SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date
3. To get MM-DD-YY use SELECT FORMAT (getdate(), 'MM-dd-yy') as date
4. Check out more examples below

The syntax of the SQL Server FORMAT function is the following:

FORMAT (value,format[,culture])
GO

SQL Server FORMAT Examples for Formatting Dates

Let's start with an example:

SELECT FORMAT (getdate(), 'dd-MM-yy') as date
GO

The format will be as follows:

• dd - day number from 01-31
• MM - month number from 01-12
• yy - two digit year number

If this was run for March 21, 2021 the output would be: 21-03-21.

Let's try another one:

SELECT FORMAT (getdate(), 'hh:mm:ss') as time
GO

The format will be as follows:

• hh - hour of day from 01-12
• mm - minutes of hour from 00-59
• ss - seconds of minute from 00-59

The output will be: 02:48:42.

SQL Server Date FORMAT output examples

Below is a list of date and datetime formats with an example of the output.  The current date used for all of these examples is "2021-03-21 11:36:14.840".

As you can see, we used a lot of options for the date and time formatting, which are listed below.

• dd - this is day of month from 01-31
• dddd - this is the day spelled out
• MM - this is the month number from 01-12
• MMM - month name abbreviated
• MMMM - this is the month spelled out
• yy - this is the year with two digits
• yyyy - this is the year with four digits
• hh - this is the hour from 01-12
• HH - this is the hour from 00-23
• mm - this is the minute from 00-59
• ss - this is the second from 00-59
• tt - this shows either AM or PM
• d - this is day of month from 1-31 (if this is used on its own it will display the entire date)
• us - this shows the date using the US culture which is MM/DD/YYYY

For all the different custom date and time format strings to use with the SQL Server FORMAT command, check out this list.

Below we show examples of how to get different formats using these options:

• dd - this is day of month from 01-31
• MM - this is the month number from 01-12
• yyyy - this is the year with four digits

SQL date format dd/MM/yyyy with SQL FORMAT

The following example shows how to get a dd/MM/yyyy date format, such as 30/04/2008 for April 4, 2008:

--MSSQLTips.com 
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'dd/MM/yyyy') AS FormattedDate
FROM 
    Sales.Currency;

The output is the following:

Format date SQL MM/dd/yyyy with SQL FORMAT

The following example shows how to get a MM/dd/yyyy date format, such as 04/30/2008 for April 4, 2008:

--MSSQLTips.com 
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'MM/dd/yyyy') AS FormattedDate
FROM 
    Sales.Currency;

The output is the following:

SQL date format yyyy MM dd with SQL FORMAT

If we want to change to the yyyy MM dd FORMAT using the format function, the following example can help you to do it.

--MSSQLTips.com
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'yyyy MM dd') AS FormattedDate
FROM 
    Sales.Currency;

The output for the query is the following:

SQL date format yyyyMMdd with SQL FORMAT

The yyyyMMdd format is also a commonly used format to store data in the database, for software development comparisons, financial systems, etc.

The following example, shows how to use this format.

--MSSQLTips.com 
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'yyyyMMdd') AS FormattedDate
FROM 
    Sales.Currency;

The output of the query is the following:

SQL format date ddMMyyyy with SQL FORMAT

The ddMMyyyy format is common in countries like England, Ireland, Australia, New Zealand, Nepal, Malasia, Hong Kong, Qatar, Arabia Saudi and several other countries.

The following example shows how to use it.

--MSSQLTips.com 

SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'ddMMyyyy') AS FormattedDate
FROM 
    Sales.Currency;

The output of the query is the following:

SQL format date yyyy-MM-dd with SQL FORMAT

The yyyy-MM-dd format is commonly used in USA, Canada, Mexico, Central America and other countries.

The following example shows how to use this format:

--MSSQLTips.com 
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'yyyy-MM-dd') AS FormattedDate
FROM 
    Sales.Currency;

The output of this format is the following:

The next example will create a view with the yyyy-MM-dd format.

--MSSQLTips.com 
 
CREATE VIEW dbo.CurrencyView
AS
SELECT
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'yyyy-MM-dd') AS FormattedDate
FROM
    Sales.Currency;

The output is the following:

SELECT * FROM dbo.CurrencyView;

There are some considerations that you must take with the yyyy-mm-dd format.

SQL date format MM/dd/yyyy with SQL FORMAT

Finally, we have the MM/dd/yyyy commonly used in USA, Canada, Mexico, Central America and other countries.

The following example shows how to use it:

--MSSQLTips.com 
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'MM/dd/yyyy') AS FormattedDate
FROM 
    Sales.Currency;

The format displayed is the following:

SQL Server FORMAT Date Data Type Examples

The following shows how to format dates to mimic with different data types like Date, DateTime, DateTime2, smalldatetime, and time.

SQL Server Date FORMAT with Culture

Another option for the FORMAT function is culture. With the culture option you can obtain regional formatting. Here is a list of culture codes to use with FORMAT.

For example in the USA, the format would be like:

SELECT FORMAT (getdate(), 'd', 'en-us') as date
GO

In the USA the format is month, day, year.

If this was run for March 21, 2021 the output would be: 3/21/2021

Another example where we will use the Spanish culture in Bolivia (es-bo):

SELECT FORMAT (getdate(), 'd', 'es-bo') as date
GO

In Bolivia the format is day, month, year.

If this was run for March 21, 2021 the output would be: 21/03/2021.

The following table contains different examples for different cultures for October 11, 2021:

For a complete list of possible languages, refer to the following link:

• Default Input Profiles (Input Locales) in Windows

SQL Format Number Examples

The format also allows to format numbers according to the culture. The following table will show different examples.

Conclusion

In this article, we saw different examples to change the output for different formats in an MS SQL database.

Note: The FORMAT function uses Common Language Runtime (CLR) and there have been noticeable performance differences between other approaches (CONVERT Function, CAST Function, etc.) showing that FORMAT is much slower.

Next Steps

• You can now work with a more flexible and intuitive function to handle date formats.
• Here is more information on the FORMAT function
• Learn about more SQL Server Functions for date values:
  • SELECT CONVERT Examples
  • SQL Server Date and Time Functions with Examples
  • DATEDIFF SQL Server Function
  • Determine SQL Server Date and Time Parts with DATEPART and DATENAME Functions
  • Add and Subtract Dates using DATEADD in SQL Server
  • Update only Year, Month or Day in a SQL Server Date
  • SQL Convert Date to YYYY-MM-DD HH:MM:SS
  • Compare SQL Server Features - Decimal vs Numeric, Timestamp vs Rowversion, Unique Index vs Unique Constraint
  • New Date and Time Value Functions in SQL Server 2012

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips