SQL SERVER – Beginning SQL 2012 – Basics of CONVERT and FORMAT Function – Abstract from Joes 2 Pros Volume 4

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 4

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

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.

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 5th 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 12th 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 (http://blog.SQLAuthority.com)

About these ads

One thought on “SQL SERVER – Beginning SQL 2012 – Basics of CONVERT and FORMAT Function – Abstract from Joes 2 Pros Volume 4

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s