SQL SERVER – Beginning SQL 2012 – Basics of CONVERT FORMAT Function

From the September 17, 2011 blog post on the new SQL 2012 FORMAT function we learned how to format currency and time using different cultures. This is an improvement on what came before and also gives us new possibilities for getting date labels without needing to use DATEPART. In this post we will compare the FORMAT function to the previous techniques and also show you an easy way to grab the part of the date you need for reports. Let us learn about CONVERT FORMAT Function.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 4” in the file SQLQueries2012Vol4Chapter8.1-10Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”

CONVERT function basics

Do you think January 5^th 2013 should be shown as 1/5/2013 or 5/1/2013? The best answer is to each their own. The FORMAT function lets you pick the style, or better yet it can be relevant to the culture of the computer running the query.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 101) AS CharDate
 FROM Employee

	EmpID	FirstName	LastName	LocationID	ManagerID	CharDate
1	1	Alex	Adams	1	4	01/01/2001
2	2	Barry	Brown	1	11	08/12/2002
3	3	Lee	Osako	2	11	09/01/1999
4	4	Dave	Kinnison	1	11	03/16/1996
5	5	Eric	Bender	1	11	05/17/2007
6	6	Lisa	Kendall	4	4	11/15/2001
						20 rows

It looks like Barry Brown’s hire date is August 12, 2002. That is because in the USA culture we specify month/day/year which makes 08/12/2002 as August 12^th 2002. However, if we were in Europe (where it’s day/month/year), 08/12/2002 would look a lot like December 8, 2002.

Let’s try a different formatting style using the CONVERT function. By changing the formatting style from 101 to 103 in the same query, notice we retrieve the date in the day, month, and then year.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 103) AS CharDate
 FROM Employee

	EmpID	FirstName	LastName	LocationID	ManagerID	CharDate
1	1	Alex	Adams	1	4	01/01/2001
2	2	Barry	Brown	1	11	12/08/2002
3	3	Lee	Osako	2	11	01/09/1999
4	4	Dave	Kinnison	1	11	16/03/1996
5	5	Eric	Bender	1	11	17/05/2007
6	6	Lisa	Kendall	4	4	15/11/2001
						20 rows

As we saw, style 103 returns the European day/month/year, while style 109 returns 3 letter abbreviated month/integer day/ integer year as seen in the comparison.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 103) AS CharDate
 FROM Employee
 WHERE EmpID = 2

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 109) AS CharDate
 FROM Employee
 WHERE EmpID = 2

FORMAT with Culture Dates Basics

Now we will take the previous code and utilize the new FORMAT function. Instead of going through the process of converting the field we will simply set the format we are looking for. Here we replaced CONVERT with FORMAT and instead of naming it an NVARCHAR and picking a style, we simply (in parentheses) name the field, and in single quotes we set the parameter ‘d’ for day to achieve the same result.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 101) AS CharDate
 FROM Employee
 WHERE EmpID = 4

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 FORMAT(HireDate, 'd') AS CharDate
 FROM Employee
 WHERE EmpID = 4

In that last example we choose d for day. You can also choose m (month) to return the month and day or y (year) as your parameters to return month and year. In all the examples so far the style is in the “en-us” culture because that’s the culture set on the server we used. It is not normally necessary to set this function however; there is a third optional parameter. What if we had done ‘en-gb’ for Great Britain? You can see here, the result set has a [HireDate] of 16/03/1996 and the numbers are separated with forward slashes ‘/’ as would be customary in Great Britain.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 FORMAT(HireDate, 'd', 'en-gb') AS CharDate
 FROM Employee
 WHERE EmpID = 4

	EmpID	FirstName	LastName	LocationID	ManagerID	CharDate
1	4	Dave	Kinnison	1	11	16/03/1996
						1 rows

Let’s change it up a bit more. Let’s use the style format ‘de-de’, for German, Germany. What does that look like? We can see it gives us day, month and year, separated by dots as would be customary in Germany.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 FORMAT(HireDate, 'd', 'de-de') AS CharDate
 FROM Employee
 WHERE EmpID = 4

	EmpID	FirstName	LastName	LocationID	ManagerID	CharDate
1	4	Dave	Kinnison	1	11	16.03.1996
						1 rows

FORMAT with Months

You have seen what happened to the [HireDate] field when we ran it through the format with the ‘d’ and ‘en-us’. This is great for days, but what if we put a series of “MMMM” in there? Let’s put four M’s for month and simplify the fields to show just the [HireDate] and formatted fields. What will the expression field look like? As you can see, this format will pull up just the month fully spelled out. The first record says January, the second record August, and so on.

SELECT HireDate,
 FORMAT(HireDate, 'MMMM', 'en-us') AS CharDate
 FROM Employee

	HireDate	CharDate
1	2001-01-01 00:00:00.000	January
2	2002-08-12 00:00:00.000	August
3	1999-09-01 00:00:00.000	September
4	1996-03-16 00:00:00.000	March
5	2007-05-17 00:00:00.000	May
6	2001-11-15 00:00:00.000	November
		20 rows

Let’s try a few other combinations. What does three M’s look like? It shows us a 3 letter month abbreviation, like Jan, Aug, Sep. Let’s try two M’s. This format returns a two digit numeric, like 01 for January and 08 for August. What does a single M return? This returns the whole month spelled out, with the day. For example, January 01 and August 12 and so forth.

BE CAREFULL (MM is not mm)

SELECT HireDate, FORMAT(HireDate, 'MMM', 'en-us') AS CharDate
 FROM Employee

SELECT HireDate, FORMAT(HireDate, 'MM', 'en-us') AS CharDate
 FROM Employee

SELECT HireDate, FORMAT(HireDate, 'M', 'en-us') AS CharDate
 FROM Employee

	HireDate	CharDate
1	2001-01-01 00:00:00.000	Jan
2	2002-08-12 00:00:00.000	Aug
	HireDate	CharDate
1	2001-01-01 00:00:00.000	01
2	2002-08-12 00:00:00.000	08
	HireDate	CharDate
1	2001-01-01 00:00:00.000	January 01
2	2002-08-12 00:00:00.000	August 12
		60 rows

Up to this point the month, day and year formats all seem straight forward. There are a few things to be aware of that can get a little tricky. What happens if this ‘M’ is lower case? As you can see, there is no real change in the result. How about ‘mm’? Now we are getting a different result. The lower case ‘mm’ calls for a return in minutes. Since the examples returned are all zeros it’s really hard to tell exactly what this new field means because the hire dates don’t go down to the minutes. To really see this example, let’s utilize GETDATE() and we can see the formatted field returns 06 since this was run on Oct 6th, 2012 at 11:22pm.

SELECT HireDate, FORMAT(HireDate, 'mm', 'en-us') AS CharDate
 FROM Employee

SELECT GETDATE(), FORMAT(GETDATE(), 'mm', 'en-us')

HireDate	CharDate
2001-01-01 00:00:00.000	00
2002-08-12 00:00:00.000	00
(No column name)	(No column name)
2012-10-06 21:22:38.643	22
	21 rows

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (https://blog.sqlauthority.com)