Back to cookbooks list Articles Cookbook

How to Format a Date in T-SQL

  • FORMAT()


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


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

1Lisa Bank2019-01-20
2Credit Financial Institute2018-03-14
3Williams Holding2019-10-28

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.

Download SQL Server Cheat Sheet

Solution 1:

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

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

Here is the result:

Lisa Bank2019/01/20
Credit Financial Institute2018/03/14
Williams Holding2019/10/28


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:

ddday in range 01-31
MMmonth in range 01-12
yy2-digit year
yyyy4-digit year
HHhour in range 00-23
mmminutes in range 00-59
ssseconds in range 00-59

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

  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.

Recommended courses:

Recommended articles:

See also: