Articles Cookbook
Back to list
SQL Server

How to Format a Date in T-SQL

Database:

Operators:

CONVERT(), FORMAT()

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.

idnamestart_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.

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:

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

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:

codedescription
10111/30/2019
1022019.11.30
10330/11/2019
10430.11.2019
10530-11-2019
11011-30-2019
1112019/11/30

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:

patterndescription
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():

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.

Recommended courses:

Recommended articles:

See also:

go to top