How to Format Dates with FORMAT Function In SQL Server

Tutorialsrack 09/12/2020 SQL SQL Server

In this article, you will learn how to format dates with a format function in SQL server. In the SQL Server, 2008 and earlier versions of SQL Server used the CONVERT function to format the DateTime. Here you can check out, how to format the DateTime using CONVERT Function in SQL Server. SQL Server CONVERT function is not too flexible and has limited DateTime formats. 

In SQL Server 2012 and later version of SQL Server, a new function FORMAT is introduced which is quite easy and more flexible to format Dates. In this article, you will see various examples for the FORMAT function to format the Dates.

Syntax of FORMAT function

FORMAT (value,format[,culture])

Parameters are as follows:

Value – The value to be formatted.

Format – The specified format in which value will be formatted.

Culture – Culture is optional. If we did not provide the culture SQL SERVER,  it uses the default Culture of the current session.

Current DateTime Format

In these examples, we used the default SQL Datetime Format using the GETDATE() function.

Select GETDATE() as 'Current DateTime'
--Output => 2020-11-18 11:55:28.690

Examples of SQL Server FORMAT Function

Here are some examples of DateTime Formatting with the Format Function with or without Culture

Select GETDATE() as 'Current DateTime'
--Output => 2020-11-18 11:55:28.690

SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as 'Custom Date Format'
--Output => 18/11/2020 

SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as 'Custom DateTime Format'
--Output => 18/11/2020, 11:50:29 

SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as 'Custom Date Format'
--Output => Wednesday, November, 2020

SELECT FORMAT (getdate(), 'MMM dd yyyy') as 'Custom Date Format'
--Output => Nov 18 2020

SELECT FORMAT (getdate(), 'MM.dd.yy') as 'Custom Date Format'
-- Output => 11.18.20

SELECT FORMAT (getdate(), 'MM-dd-yy') as 'Custom Date Format'
--Output => 11-18-20

SELECT FORMAT (getdate(), 'hh:mm:ss tt') as 'Custom Time Format'
--Output => 11:50:29 AM

-- US Culture Date
SELECT FORMAT (getdate(), 'd','us') as 'Custom Date Format With US Culture'
--Output => 11/18/2020

-- Bolivia Culture Date
SELECT FORMAT (getdate(), 'd', 'es-bo') as 'Custom Date Format With Bolivia Culture'
--Output => 18/11/2020

-- Norwegian Culture Date
SELECT FORMAT (GETDATE(), 'd', 'no') AS 'Custom Date Format With Norwegian Culture';
--Output => 18.11.2020

-- Zulu Culture Date
Select FORMAT (GETDATE(), 'd', 'zu') AS 'Custom Date Format With Zulu Culture';
--Output => 11/18/2020

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

I hope this article will help you to understand how to format dates with a format function in SQL server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts