Format SQL Server Dates with FORMAT Function

By:   |   Updated: 2023-10-18   |   Comments (18)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Dates


Problem

Microsoft SQL Server 2008 and earlier versions used the CONVERT functions to handle date formatting in SQL queries, SELECT statements, stored procedures and T-SQL scripts. In this tip, Date and Time Conversions Using SQL Server, we have a list of the available examples on how to use the CONVERT function to handle different date formats in a SQL database.

As you may know, the CONVERT function is not very flexible and we have limited date formats. In Microsoft SQL Server 2012 and later, the function FORMAT has been introduced which is much easier to use to format dates.  This tutorial shows different examples of using this new function to format dates.

Solution

Starting with SQL Server 2012, a function to handle formatting dates was introduced which is similar to Oracle's to_date function. Many Oracle DBAs complained about the SQL Server CONVERT function and its poor flexibility and now we have a new way to format dates in SQL Server.

With the SQL Server FORMAT function we do not need to know the format number to use to get the right date format we want, we can just specify the display format we want and we get that format.

SQL Date Format with the FORMAT function

  1. Use the FORMAT function to format the date and time data types from a date column (date, datetime, datetime2, smalldatetime, datetimeoffset, etc. data type) in a table or a variable such as GETDATE()
  2. To get DD/MM/YYYY use SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date
  3. To get MM-DD-YY use SELECT FORMAT (getdate(), 'MM-dd-yy') as date
  4. Check out more examples below

The syntax of the SQL Server FORMAT function is the following:

FORMAT (value,format[,culture])
GO

SQL Server FORMAT Examples for Formatting Dates

Let's start with an example:

SELECT FORMAT (getdate(), 'dd-MM-yy') as date
GO

The format will be as follows:

  • dd - day number from 01-31
  • MM - month number from 01-12
  • yy - two digit year number

If this was run for March 21, 2021 the output would be: 21-03-21.

Let's try another one:

SELECT FORMAT (getdate(), 'hh:mm:ss') as time
GO

The format will be as follows:

  • hh - hour of day from 01-12
  • mm - minutes of hour from 00-59
  • ss - seconds of minute from 00-59

The output will be: 02:48:42.

SQL Server Date FORMAT output examples

Below is a list of date and datetime formats with an example of the output.  The current date used for all of these examples is "2021-03-21 11:36:14.840".

Query Sample output
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date 21/03/2021
SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date 21/03/2021, 11:36:14
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date Wednesday, March, 2021
SELECT FORMAT (getdate(), 'MMM dd yyyy') as date Mar 21 2021
SELECT FORMAT (getdate(), 'MM.dd.yy') as date 03.21.21
SELECT FORMAT (getdate(), 'MM-dd-yy') as date 03-21-21
SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date 11:36:14 AM
SELECT FORMAT (getdate(), 'd','us') as date 03/21/2021
SELECT FORMAT (getdate(), 'yyyy-MM-dd hh:mm:ss tt') as date 2021-03-21 11:36:14 AM
SELECT FORMAT (getdate(), 'yyyy.MM.dd hh:mm:ss t') as date 2021.03.21 11:36:14 A
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy','es-es') as date --Spanish domingo, marzo, 2021
SELECT FORMAT (getdate(), 'dddd dd, MMMM, yyyy','ja-jp') as date --Japanese 日曜日 21, 3月, 2021
SELECT FORMAT (getdate(), 'MM-dd-yyyy ') as date 03-21-2021
SELECT FORMAT (getdate(), 'MM dd yyyy ') as date 03 21 2021
SELECT FORMAT (getdate(), 'yyyyMMdd') as date 20231011
SELECT FORMAT (getdate(), 'HH:mm:dd') as time 11:36:14
SELECT FORMAT (getdate(), 'HH:mm:dd.ffffff') as time 11:36:14.84000

As you can see, we used a lot of options for the date and time formatting, which are listed below.

  • dd - this is day of month from 01-31
  • dddd - this is the day spelled out
  • MM - this is the month number from 01-12
  • MMM - month name abbreviated
  • MMMM - this is the month spelled out
  • yy - this is the year with two digits
  • yyyy - this is the year with four digits
  • hh - this is the hour from 01-12
  • HH - this is the hour from 00-23
  • mm - this is the minute from 00-59
  • ss - this is the second from 00-59
  • tt - this shows either AM or PM
  • d - this is day of month from 1-31 (if this is used on its own it will display the entire date)
  • us - this shows the date using the US culture which is MM/DD/YYYY

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

Below we show examples of how to get different formats using these options:

  • dd - this is day of month from 01-31
  • MM - this is the month number from 01-12
  • yyyy - this is the year with four digits

SQL date format dd/MM/yyyy with SQL FORMAT

The following example shows how to get a dd/MM/yyyy date format, such as 30/04/2008 for April 4, 2008:

--MSSQLTips.com 
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'dd/MM/yyyy') AS FormattedDate
FROM 
    Sales.Currency;

The output is the following:

query results

Format date SQL MM/dd/yyyy with SQL FORMAT

The following example shows how to get a MM/dd/yyyy date format, such as 04/30/2008 for April 4, 2008:

--MSSQLTips.com 
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'MM/dd/yyyy') AS FormattedDate
FROM 
    Sales.Currency;

The output is the following:

query results

SQL date format yyyy MM dd with SQL FORMAT

If we want to change to the yyyy MM dd FORMAT using the format function, the following example can help you to do it.

--MSSQLTips.com
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'yyyy MM dd') AS FormattedDate
FROM 
    Sales.Currency;

The output for the query is the following:

SQL date format yyyyMMdd with SQL FORMAT

The yyyyMMdd format is also a commonly used format to store data in the database, for software development comparisons, financial systems, etc.

The following example, shows how to use this format.

--MSSQLTips.com 
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'yyyyMMdd') AS FormattedDate
FROM 
    Sales.Currency;

The output of the query is the following:

query results

SQL format date ddMMyyyy with SQL FORMAT

The ddMMyyyy format is common in countries like England, Ireland, Australia, New Zealand, Nepal, Malasia, Hong Kong, Qatar, Arabia Saudi and several other countries.

The following example shows how to use it.

--MSSQLTips.com 

SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'ddMMyyyy') AS FormattedDate
FROM 
    Sales.Currency;

The output of the query is the following:

query results

SQL format date yyyy-MM-dd with SQL FORMAT

The yyyy-MM-dd format is commonly used in USA, Canada, Mexico, Central America and other countries.

The following example shows how to use this format:

--MSSQLTips.com 
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'yyyy-MM-dd') AS FormattedDate
FROM 
    Sales.Currency;

The output of this format is the following:

query results

The next example will create a view with the yyyy-MM-dd format.

--MSSQLTips.com 
 
CREATE VIEW dbo.CurrencyView
AS
SELECT
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'yyyy-MM-dd') AS FormattedDate
FROM
    Sales.Currency;

The output is the following:

SELECT * FROM dbo.CurrencyView;
query results

There are some considerations that you must take with the yyyy-mm-dd format.

SQL date format MM/dd/yyyy with SQL FORMAT

Finally, we have the MM/dd/yyyy commonly used in USA, Canada, Mexico, Central America and other countries.

The following example shows how to use it:

--MSSQLTips.com 
 
SELECT 
    CurrencyCode,
    Name,
    FORMAT(ModifiedDate, 'MM/dd/yyyy') AS FormattedDate
FROM 
    Sales.Currency;

The format displayed is the following:

query results

SQL Server FORMAT Date Data Type Examples

The following shows how to format dates to mimic with different data types like Date, DateTime, DateTime2, smalldatetime, and time.

Data Type Query Sample Output
Date SELECT FORMAT(GetDate(), 'yyyy-MM-dd') AS Date; 2021-03-21
DateTime SELECT FORMAT(GetDate(), 'yyyy-MM-dd HH:mm:ss.fff') AS Date; 2021-03-21 19:36:22.640
SmallDateTime SELECT FORMAT( CASE WHEN DATEPART(SECOND, GetDate()) >= 30 THEN DATEADD(MINUTE, 1, GetDate()) ELSE GetDate() END, 'yyyy-MM-dd HH:mm:00' ) AS Date; -- this rounds up to the nearest minute 2021-03-21 19:44:00
DateTime2 SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss.fffffff') AS Date; 2021-03-21 19:47:15.2734243
Time SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss.fffffff') AS Date; 19:55:58.5134326

SQL Server Date FORMAT with Culture

Another option for the FORMAT function is culture. With the culture option you can obtain regional formatting. Here is a list of culture codes to use with FORMAT.

For example in the USA, the format would be like:

SELECT FORMAT (getdate(), 'd', 'en-us') as date
GO

In the USA the format is month, day, year.

If this was run for March 21, 2021 the output would be: 3/21/2021

Another example where we will use the Spanish culture in Bolivia (es-bo):

SELECT FORMAT (getdate(), 'd', 'es-bo') as date
GO

In Bolivia the format is day, month, year.

If this was run for March 21, 2021 the output would be: 21/03/2021.

The following table contains different examples for different cultures for October 11, 2021:

Culture Query Sample output
English-USA SELECT FORMAT (getdate(), 'd', 'en-US') as date 10/11/2021
French-France SELECT FORMAT (getdate(), 'd', 'fr-FR') as date 11/10/2021
Armenian-Armenian SELECT FORMAT (getdate(), 'd', 'hy-AM') as date 11.10.2021
Bosnian Latin SELECT FORMAT (getdate(), 'd', 'bs-Latn-BA') as date 11. 10. 2021.
Simplified Chinese SELECT FORMAT (getdate(), 'd', 'zh-CN') as date 2021/10/11
Danish - Denmark SELECT FORMAT (getdate(), 'MM.dd.yy') as date 11-10-2021
Dari - Afghanistan SELECT FORMAT (getdate(), 'd', 'prs-AF') as date 1400/7/19
Divehi - Maldives SELECT FORMAT (getdate(), 'd', 'dv-MV') as date 11/10/21
French - Belgium SELECT FORMAT (getdate(), 'd', 'fr-BE') as date 11-10-21
French - Canada SELECT FORMAT (getdate(), 'd', 'fr-CA') as date 2021-10-11
Hungarian - Hungary SELECT FORMAT (getdate(), 'd', 'hu-HU') as date 2021. 10. 11.
isiXhosa / Xhosa - South Africa SELECT FORMAT (getdate(), 'd', 'xh-ZA') as date 2021-10-11

For a complete list of possible languages, refer to the following link:

SQL Format Number Examples

The format also allows to format numbers according to the culture. The following table will show different examples.

Format Query Sample output
Currency-English-USA SELECT FORMAT(200.36, 'C', 'en-us') AS 'Currency Format' $200.36
Currency-Germany SELECT FORMAT(200.36, 'C', 'de-DE') AS 'Currency Format' 200,36 €
Currency-Japan SELECT FORMAT(200.36, 'C', 'ja-JP') AS 'Currency Format' ¥200
General Format SELECT FORMAT(200.3625, 'G', 'en-us') AS 'Format' 200.3625
Numeric Format SELECT FORMAT(200.3625, 'N', 'en-us') AS 'Format' 200.36
Numeric 3 decimals SELECT FORMAT(11.0, 'N3', 'EN-US') AS 'Format' 11.000
Decimal SELECT FORMAT(12, 'D', 'en-us') AS 'Format' 12
Decimal 4 SELECT FORMAT(12, 'D4', 'en-us') AS 'Format' 0012
Exponential SELECT FORMAT(120, 'E', 'EN-US') AS 'Format' 1.200000E+002
Percent SELECT FORMAT(0.25, 'P', 'EN-US') AS 'Format' 25.00%
Hexadecimal SELECT FORMAT(11, 'X', 'EN-US') AS 'Format' B

Conclusion

In this article, we saw different examples to change the output for different formats in an MS SQL database.

Note: The FORMAT function uses Common Language Runtime (CLR) and there have been noticeable performance differences between other approaches (CONVERT Function, CAST Function, etc.) showing that FORMAT is much slower.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-10-18

Comments For This Article




Monday, January 9, 2023 - 10:44:11 AM - Jeff Moden Back To Top (90808)
Thanks for the update, Greg.

Tuesday, July 19, 2022 - 11:41:58 AM - Greg Robidoux Back To Top (90285)
Hi Jeff, thanks for your continued education on the use of FORMAT. There is a statement in the article that lets people know this is an issue. I agree that if this is used against millions of rows there will be a big performance hit and that there are other options in SQL Server as the article points out.

Thanks
Greg

Tuesday, July 19, 2022 - 11:18:47 AM - Jeff Moden Back To Top (90282)
They keep republishing this article without any warnings about how the use of FORMAT will make the code at least 20 times slower than using CONVERT or any of another myriad ways to format dates. Caveat Emptor, folks!

Wednesday, January 5, 2022 - 1:48:19 AM - MD SAFIQUL ISALM Back To Top (89630)
very essential post

Wednesday, November 17, 2021 - 10:29:26 AM - Don Maes Back To Top (89459)
Here is a list of the commands I use on a regular basis to convert dates and times.

/*
Formatting dates and times

ctr dateinfo descr
100 Nov 17 2021 10:23AM convert(varchar(50),getdate(),100)
101 11/17/2021 convert(varchar(50),getdate(),101)
102 2021.11.17 convert(varchar(50),getdate(),102)
103 17/11/2021 convert(varchar(50),getdate(),103)
104 17.11.2021 convert(varchar(50),getdate(),104)
105 17-11-2021 convert(varchar(50),getdate(),105)
106 17 Nov 2021 convert(varchar(50),getdate(),106)
107 Nov 17, 2021 convert(varchar(50),getdate(),107)
108 10:23:43 convert(varchar(50),getdate(),108)
109 Nov 17 2021 10:23:43:110AM convert(varchar(50),getdate(),109)
110 11-17-2021 convert(varchar(50),getdate(),110)
111 2021/11/17 convert(varchar(50),getdate(),111)
112 20211117 convert(varchar(50),getdate(),112)
113 17 Nov 2021 10:23:43:110 convert(varchar(50),getdate(),113)
114 10:23:43:110 convert(varchar(50),getdate(),114)
120 2021-11-17 10:23:43 convert(varchar(50),getdate(),120)
121 2021-11-17 10:23:43.110 convert(varchar(50),getdate(),121)
126 2021-11-17T10:23:43.110 convert(varchar(50),getdate(),126)
130 12 ???? ?????? 1443 10:23:43:110AM convert(varchar(50),getdate(),130)
131 12/04/1443 10:23:43:110AM convert(varchar(50),getdate(),131)
0 20211117102343 replace(replace(replace(convert(varchar(50),getdate(),120),' ',''),'-',''),':','')
1 1023 replace(convert(varchar(5),getdate(),108),':','')

*/
declare @ctr int
select @ctr = 100
create table #temp (ctr int, dateinfo varchar(50), descr varchar(100))

while @ctr < 115
begin
insert into #temp
select @ctr as 'Counter', convert(varchar(50),getdate(),@ctr), 'convert(varchar(50),getdate(),'+convert(varchar(3),@ctr)+')'
select @ctr = @ctr + 1
end
select @ctr = 120
while @ctr < 122
begin
insert into #temp
select @ctr as 'Counter', convert(varchar(50),getdate(),@ctr), 'convert(varchar(50),getdate(),'+convert(varchar(3),@ctr)+')'
select @ctr = @ctr + 1
end
select @ctr = 126
insert into #temp
select @ctr as 'Counter', convert(varchar(50),getdate(),@ctr), 'convert(varchar(50),getdate(),'+convert(varchar(3),@ctr)+')'
select @ctr = 130
insert into #temp
select 130 as 'Counter', convert(varchar(50),getdate(),130), 'convert(varchar(50),getdate(),'+convert(varchar(3),@ctr)+')'
select @ctr = 131
insert into #temp
select 131 as 'Counter', convert(varchar(50),getdate(),131), 'convert(varchar(50),getdate(),'+convert(varchar(3),@ctr)+')'
select @ctr = 120
insert into #temp
select 0, replace(replace(replace(convert(varchar(50),getdate(),120),' ',''),'-',''),':',''),'replace(replace(replace(convert(varchar(50),getdate(),120),'' '',''''),''-'',''''),'':'','''')'
select @ctr = 108
insert into #temp
select 1 as 'Counter', replace(convert(varchar(5),getdate(),108),':',''), 'replace(convert(varchar(5),getdate(),108),'':'','''')'

select * from #temp


drop table #temp


Thursday, November 4, 2021 - 9:58:02 AM - Steve Back To Top (89410)
Can you update your second date sample? SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date for 2 PM would return 02:00:00. It needs either HH for hours or tt for AM/PM, preferably HH since no other examples have it.

Monday, October 25, 2021 - 4:51:56 PM - Jeff Moden Back To Top (89358)
@Greg,

I might be missing it but, if I am, others will miss it, as well.

You said back in December of 2018 that you would add a warning (and it should be a significant warning) about how comparatively terrible FORMAT is for just about everything even compared to relative complex CONVERT and SUBSTRING (etc) functionality.

Some of the Developers that work with me have referenced this very article and I'm constantly having to prove to people that FORMAT should generally be avoided.

Can you add the performance warning in nice bold letters at the beginning of this article, please? Thank you.

Tuesday, July 20, 2021 - 4:10:33 AM - Torben Iisager Back To Top (89029)
Hi.
How to display timebetween, eg. 1. january 06:23PM until 3. january 09:34PM
I would like output to be in
<number of days> - <hh:mm:ss>
2 - 03:11:00
is that posible?

Wednesday, March 18, 2020 - 3:48:23 PM - Bill New Back To Top (85143)

Assuming getdate() returns "2018-03-21 11:36:14.840" as in your example.

For sorting dates later, placing the date in Year, Month, and Day order works much better. Such as:

   SELECT FORMAT (getdate(), 'yyyy/MM/dd') as date

Output 

   "2018/03/21"

---

Non-European Dates are much easier to understand if the target audience is not in Europe.


Monday, March 2, 2020 - 9:31:31 PM - Aaron Back To Top (84890)

Hi guys,

I am just trying to update the output to the spreadsheet of the date from the current standard format to format # 6.

See code snippit

Can anyone assist please.

declare @date datetime

declare @monday datetime

select @date = GETDATE()

SET DATEFIRST 2

select @monday = convert(date, DATEADD(day, 7 - DATEPART(dw, @Date), @Date), 105)

select patients.firstname as 'Name', replace(patients.mobile, ' ', '') as 'Mobile', 'Hi ' + patients.firstname + ' your upcoming appointment at Dental1 Craigieburn is on ' + cast(app_date as varchar(10)) + ' at ' + min(cast([start] as varchar(5))) + '. Reply "Y" if this is still suitable or call 9021 8928 to reschedule. Please note you may receive another message if we do not receive a reply as to your attendance.' as 'Confirmation'

from a_appointments

join "patients"

on patients.patient_id = a_appointments.pat_id

join "app_books"

on a_appointments.app_book_id = app_book_number

where app_date >= @monday and app_date < @monday + 7 and LEN(patients.mobile) > 9 and app_book_number != 5 and a_appointments.status like '%G%' and a_appointments.status not like '%K%'

group by appoint_id, app_date, app_book_id, patients.firstname, app_books.app_book_description, patients.mobile

order by app_date, min(cast([start] as varchar(5)));

output to Craigieburn_NextweekconfirmList.csv quote '' with column names

format ascii


Tuesday, December 3, 2019 - 12:06:22 AM - David H. Back To Top (83258)

Hi.

For input to an automated appointment reminder voice message, how would I convert the date from 12/5/2019 to Dec 05th, as in "you have an appointment on Dec 05th" (or 3rd or 13th, etc)? 


Sunday, January 6, 2019 - 11:19:09 PM - Nguyen Thi Xuan Back To Top (78643)

 Hi 

I have query in SQL: SELECT FORMAT (getdate(), 'dd-MMM-yy') as date

I expected the result as date when export it to excel via function export excel on asp.net but the result returns it as  text ('31-Dec-18) instead of date  (31-12-18). I mean that , it should remove comma (') in beginning of result

Could you help me  how to export it as date with format dd-mmm-yy (31-Dec-18) in excel file

Many thanks 

Xuan


Monday, December 10, 2018 - 10:45:06 AM - Greg Robidoux Back To Top (78443)

Hi Doug, thanks for pointing out the error in the code.  The code above has been updated.

-Greg


Monday, December 10, 2018 - 8:55:28 AM - Doug Back To Top (78439)

In the 4 bulleted items at the top of the article, #2 states: To get DD-MM-YYYY use SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date. This is incorrect and would yield DD/MM/YYYY. Also, it should possibly be mentioned that FORMAT is a CLR function and, as such, is considerably slower than using CONVERT.

Ultimately, dates SHOULD be formatted by the front end (e.g. SSRS, Excel, .Net application, etc.) but if you are using it in a query and you've got thousands of rows with multiple dates/row to format, you are going to see a performance degradation.


Thursday, December 6, 2018 - 12:34:46 PM - Greg Robidoux Back To Top (78416)

Thanks clarkvera.

We will update the tip based on your suggestion.


Thursday, December 6, 2018 - 10:27:23 AM - clarkvera Back To Top (78413)

 It would be MUCH more useful if you used a date with a day that would obviously not be confused with a month. Like the March 21 instead of March 7.


Monday, July 2, 2018 - 2:05:45 PM - Melanie Leyrer Back To Top (76504)

I am new to SQL Server and want to create good habits for future business solutions.  I have an Access Database front end with a new SQL Server backend.  My customer wants to have same functionality to change an assignment time in their form by just typing "9a" and pressing the [Tab] key to get the converted result of 9:00 am (i.e., a Medium Date field in MS Access).  After moving the table data into SQL Server, it resulted in a datetime() format i.e., 1899-12-30 07:00:00.000  I have modified the column to be time(0) time(7) but MS Access still doesn't allow the conversion of their entry to medium date.  I have many forms and VBA Code to adjust if I can't find a simple solution.  In the meantime, I have created unbound text boxes which allow them to to type "9a" and convert the entry to a time value.  However, if the end-user types an inaccurate value, it will error.  So I created another process control to simply provide the correct values to choose from (i.e., 9:00 am, 9:15 am, etc.) after their unbound combo box selection it will update the table with a correct time format.  This is over the top on my part, but I don't have any other solution.  Any suggestions? 


Tuesday, May 1, 2012 - 9:47:08 AM - Mike Angelastro Back To Top (17219)

You missed a universal date format.  Sometimes the date must evaluated in a SELECT statement regardless of geographic location.

 















get free sql tips
agree to terms