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.
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.
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
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!
Comments