Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

SQL Server Performance Monitoring in the Cloud

Monitor and Troubleshoot SQL Server Performance

5 ways monitoring SQL Server Agent Jobs can make your job easier

Deploying Applications in Azure without Configuration Limitations

New SQL Server Function to Format Dates

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (1)   |   Related Tips: More > Dates

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 notice, this function is not very flexible and we have limited date formats. In SQL Server 2012 and future versions, a new function FORMAT has been introduced which is much easier to use.  This article shows different examples of using this new function.


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

With the FORMAT function we do not need to know the format number, we can just specify the format that we want and we get that format.

The syntax of the FORMAT function is the following:

FORMAT (Value,format[,culture])

FORMAT Samples

Let's start with an example:

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

The format is day-month-year. The output will be: 07-03-12.

Let's try another one:

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

The format is hours:minutes:seconds. The output will be: 02:48:42.

Culture Option

Another option for this function is the culture. With the culture option you can obtain regional formatting.

For example the hour in USA format is:

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

The output is: 3/7/2012

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

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

The output is: 07/03/2012 which is different because each country has its own format.  In Bolivia the format is day, month, year where in the United States it is month, day, year.

For a complete list of values for cultures, tale a look at this article language codes.

More examples for the FORMAT function


Sample output

select FORMAT (getdate(), 'dd/MM/yyyy ') as date 07/03/2012
select FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date 07/03/2012, 11:33:13
select FORMAT (getdate(), 'dddd, MMMM, yyyy') as date Wednesday, March, 2012
select FORMAT (getdate(), 'MMM dd yyyy') as date Mar 07 2012
select FORMAT (getdate(), 'MM.dd.yy') as date 03.07.12
select FORMAT (getdate(), 'MM-dd-yy') as date 03-07-12
select FORMAT (getdate(), 'hh:mm:ss tt') as date 11:36:14 AM
select FORMAT (getdate(), 'd','af') as date 2012/03/07
Next Steps

Last Update: 5/1/2012

About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Tuesday, May 01, 2012 - 9:47:08 AM - Mike Angelastro Read The Tip

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


Sponsor Information