Rajendra Gupta
SQL Format Date using Culture

A comprehensive guide to the SQL Format function

March 11, 2020 by

This article explains the usage of SQL Format function and performance comparison with SQL CONVERT.

Introduction

In the article SQL Convert Date functions and formats, we discussed the usage of SQL CONVERT function for converting date and time into multiple formats. We have a new function, SQL FORMAT, from SQL Server 2012.

We use this new function to get output in a specified format and Culture. It returns an NVARCHAR data type in the output.

Syntax of SQL FORMAT Function

FORMAT (value, format [, culture])

It has the following parameters:

  • Value: It is the value to do formatting. It should be in support of the data type format. You can refer to Microsoft documentation for a list of supported data types and their equivalent data type
  • Format: It is the required format in which we require the output. This parameter should contain a valid .NET format string in the NVARCHAR data type. We can refer to Format types in .NET for more details
  • Culture: It is an optional parameter. By default, SQL Server uses the current session language for a default culture. We can provide a specific culture here, but the .Net framework should support it. We get an error message in case of invalid Culture

We use the following SQL CONVERT function to get output in [MM/DD/YYYY] format:

SQL CONVERT function

As we know, we require format code in SQL Convert function for converting output in a specific format.

We do not require format code in SQL FORMAT function. Let’s look at various examples of the FORMAT function:

Format String and description

Query

Output

d – It shows the day of the month from 1 through 31.

d – It shows the day of the month from 1 through 31.

D – It gives a detailed output in Weekday, Month, Date, Year format.

D – It gives a detailed output in Weekday, Month, Date, Year format.

f- It adds timestamp as well in the output of D parameter.it does not include seconds information.

f- It adds timestamp as well in the output of D parameter.it does not include seconds information.

F- It adds seconds (ss) information also in the output generated from f parameter.

F-  It adds seconds (ss) information also in the output generated from f parameter.

g:- It gives output in MM/DD/YYYY hh: mm AM/PM.

g:- It gives output in MM/DD/YYYY hh: mm AM/PM

G: Output format MM/DD/YYYY hh:mm: ss AM/PM.

G: Output format MM/DD/YYYY hh:mm: ss AM/PM

M/m: Output format-

Month date

M/m: Output format-  Month date

O – Output format

yyyy-mm-ddThh:mm:ss.nnnnnnn

O – Output format 
yyyy-mm-ddThh:mm:ss.nnnnnnn

R – Output format Day, dd Mon yyyy hh:mm:ss GMT

R – Output format Day, dd Mon yyyy hh:mm:ss GMT

S : Output format yyyy-mm-ddThh:mm:ss

S : Output format yyyy-mm-ddThh:mm:ss

U : Output format yyyy-mm-dd hh:mm:ssz

U : Output format yyyy-mm-dd hh:mm:ssz

U : Output format Day, Mon dd , yyyy hh:mm:ss AM/PM

U : Output format Day, Mon dd , yyyy hh:mm:ss AM/PM

T : Output format hh:mm:ss AM/PM

T : Output format hh:mm:ss AM/PM

t : Output format hhLmm AM/PM

t : Output format hhLmm AM/PM

Y: Output format

Mon yyyy

Y: Output format
Mon yyyy

Output format – MM/dd/yy

Output format - MM/dd/yy

Output format – MMdd/yyyy

Output format - MMdd/yyyy

Output format -yy.MM.dd

Output format -yy.MM.dd

Output format yyyy.MM.dd

Output format 0Yyyy.MM.dd

Output format -dd/MM/yy

Output format -dd/MM/yy

Output format -dd/MM/yy

Output format -dd/MM/yy

Output format –

dd-MM-yyyy

Output format –
dd-MM-yyyy

Output format –

dd MMM yy

Output format –
dd MMM yy

Output format –

dd MMM yyyy

Output format –
dd MMM yyyy

Output format –

MMM dd yyyy

Output format –
MMM dd yyyy

Output format –

HH:mm:ss

Output format –
HH:mm:ss

Output format –

MMM d yyyy h:mm:ss

Output format –
MMM d yyyy h:mm:ss

Output format –

Dd MMM yyyy HH:mm:ss

Dd MMM yyyy HH:mm:ss

Output format –

yyyy-MM-dd HH:mm:ss

yyyy-MM-dd HH:mm:ss

Output format –

MM/dd/yy h:mm:ss tt

Output format –
MM/dd/yy h:mm:ss tt

Output format –

yy-M-d

Output format –
yy-M-d

Output format –

M-d-yyyy

Output format –
M-d-yyyy

Output format –

d-M-yyyy

Output format –
d-M-yyyy

Output format –

d-M-yy

Output format –
d-M-yy

Output format –

yyyy/M/d

Output format –
yyyy/M/d

Output format –

MM.dd.yyyy

Output format –
MM.dd.yyyy

Output format –

MMMM dd,yyyy

Output format –
MMMM dd,yyyy

SQL Format Date using Culture

In the previous section, we did not use the culture argument. As you know, it is an optional argument, so let’s see what difference it makes if we specify it in a query.

In the below query, we see date format using d argument for different cultures. You can refer to the table for culture codes.

SQL Format Date using Culture

Similarly, if we change the format from d to f in the above query, it gives the following output:

query output

SQL Format Currency using Culture

We can also format currency using this function. Suppose you manage an online shopping portal where customers from a different country come for shopping. You want to display the product prices in their currency. In the following query, we use the FORMAT function for showing pricing with a currency symbol:

Currency using Culture

Number format using FORMAT function

Number format using FORMAT function

Escaping Colons and Periods in SQL FORMAT function

We should avoid colons and periods in this function, and it is adhering to the .NET CLR rules as well. We can use colons and period as the second parameter, and the first parameter should be a backslash. In the following example, let us see the second format statement skips the colon in the time specified:

Escaping Colons and Periods

Performance comparison of SQL FORMAT and SQL CONVERT function

We explored the use cases of FORMAT function. You might think we should stop using the SQL CONVERT function and start using the SQL FORMAT. Wait! Let’s make a comparison of both SQL FORMAT and SQL CONVERT.

For performance comparison, create a table and insert data into it:

Now, execute the following SQL queries:

  • Query1: Select all records from the TestPerfomance order by ID column:

  • Query 2: Select all records from TestPerfomance order by ID column and use convert function for the InputTime column:

  • Query 3: Select all records from TestPerfomance order by ID column and use convert function for the InputTime column:

  • Query4: Select all records from TestPerfomance order by ID column and use FORMAT function for the InputTime column:

We can use DMV sys.dm_exec_query_stats and sys.dm_exec_sql_text to get the performance comparison data of the select statements we executed above.

We get the following output from the DMV:

Performance comparison

To understand it better, let’s view this data in a graph:

Performance comparison graph

Look at the graph for query 2, 3 and 4.

  • We get high elapsed time for the query that uses SQL FORMAT function
  • Queries that use the CONVERT function have better performance compare to FORMAT function
  • We also see total_clr_time for the query with FORMAT function while it is zero for CONVERT function queries because the format function uses .Net CLR runtime

Conclusion

SQL FORMAT function is useful to convert the dates, time, number, currency in a specified format. We should use it when we require locale-aware changes only as it might cause performance issues. We should use the SQL CONVERT function for all other cases. If you plan to use it in production, I would recommend doing a thorough performance testing for your workload.

Rajendra Gupta
3,646 Views