How to Format a Date in T-SQL

Problem:

You’d like to display a date value in another format in SQL Server.

Example:

Our database has a table named company with data in the columns id (primary key), name, and start_date.

For each company, let’s convert their start date to a new format, YYYY/MM/DD, where YYYY is a 4-digit year, MM is a 2-digit month, and DD is a 2-digit day.

Solution 1:

We’ll use the CONVERT() function. Here’s the query you’d write:

SELECT 
  CONVERT(NVARCHAR, start_date, 111) AS new_date
FROM company;

Here is the result:

Discussion:

Use the CONVERT() function to change the format of a date from a given column or expression.

This function takes three arguments:

1. The new data type (in our example, NVARCHAR).
2. An expression or column name containing the date to format (in our example, the start_date column).
3. An optional style code, as an integer. (In our example, the 111 style displays the date using slashes to separate the parts.)

The table below presents the most popular date style codes:

A comprehensive list of format styles can be found in the T-SQL documentation.

The query above changed the format of Lisa Bank’s date 2019-01-20 to a string containing the date 2019/01/20.

Solution 2:

In SQL Server 2012 and later, you can use the FORMAT() function to change date/time formats. You use the characters below to specify the desired format:

Here’s the query you would write using FORMAT():

SELECT 
  FORMAT(start_date, 'yyyy-MM-dd') AS new_date
FROM company;

The first argument is the datetime/date/time value to reformat. The second is a string containing the pattern of the new format. This function returns an NVARCHAR data type. Use FORMAT() if you work on SQL Server 2012 or later and want to convert dates/times to strings containing the formatted dates/times.