Format SQL Server Dates with FORMAT Function

By: Daniel Calbimonte | Updated: 2018-12-06 | Comments (11) | Related: 1 | 2 | 3 | 4 | 5 | More > Dates

Problem

SQL Server 2008 and earlier versions used the CONVERT functions to handle date formatting. 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.

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

Solution

Starting with SQL Server 2012, a new 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.

How to format SQL Server dates with FORMAT function

Use the FORMAT function to format the date and time
To get DD/MM/YYYY use SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date
To get MM-DD-YY use SELECT FORMAT (getdate(), 'MM-dd-yy') as date
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, 2018 the output would be: 21-03-18.

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 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, 2018 the output would be: 3/21/2018

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, 2018 the output would be: 21/03/2018.

SQL Server FORMAT output examples

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

Query	Sample output
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date	21/03/2018
SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date	21/03/2018, 11:36:14
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date	Wednesday, March, 2018
SELECT FORMAT (getdate(), 'MMM dd yyyy') as date	Mar 21 2018
SELECT FORMAT (getdate(), 'MM.dd.yy') as date	03.21.18
SELECT FORMAT (getdate(), 'MM-dd-yy') as date	03-21-18
SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date	11:36:14 AM
SELECT FORMAT (getdate(), 'd','us') as date	03/21/2018

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.

Next Steps

You can now work with a more flexible and intuitive function to handle date formats.
Here is more information on the FORMAT function

Last Updated: 2018-12-06

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips

Related Resources

Comments For This Article

Wednesday, December 09, 2020 - 12:58:56 PM - Jeff Moden	Back To Top (87895)
Tip Comments Pending Approval

Wednesday, March 18, 2020 - 3:48:23 PM - Bill New

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 02, 2020 - 9:31:31 PM - Aaron

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 03, 2019 - 12:06:22 AM - David H.

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 06, 2019 - 11:19:09 PM - Nguyen Thi Xuan

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

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

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 06, 2018 - 12:34:46 PM - Greg Robidoux

Thanks clarkvera.

We will update the tip based on your suggestion.

Thursday, December 06, 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 02, 2018 - 2:05:45 PM - Melanie Leyrer

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 01, 2012 - 9:47:08 AM - Mike Angelastro

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