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.
The syntax of the FORMAT function is the following:
FORMAT (Value,format[,culture]) GO
Let's start with an example:
select FORMAT (getdate(), 'dd-MM-yy') as date GO
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 GO
The format is hours:minutes:seconds. The output will be: 02:48:42.
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 GO
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 GO
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
|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|
- You can now work with a more flexible and intuitive function to handle date formats.
- Here is more information on the FORMAT function: http://msdn.microsoft.com/en-us/library/hh213505(v=sql.110).aspx
Last Update: 5/1/2012
About the author
View all my tips