Getting the name of a month from the month number in SQL Server

May 23, 2011 by Victoria Yudin 18 Comments

I was creating a SmartList for a customer recently and was using the GL11110 and GL11111 views in GP for monthly summaries of P&L accounts, but wanted to show the month names instead of period ID’s to make it a bit more user-friendly. I could have linked to the fiscal period setup table to get the period names, but many companies do not actually change the period names there, so all I would have gotten would be ‘Period 1’, ‘Period 2’, etc.

Instead of ‘hard-coding’ these with a case statement I thought I would try to find a way to do this more elegantly. So I did some searching on the internet and put together the SQL statement below. I thought I would share it to save others time:

SELECT DATENAME(month, DATEADD(month, GL.PERIODID, -1 ))

You can see how to do this without writing code using Crystal Reports and SSRS in the May issue of our GP Reports Viewer Newsletter.

More tips like this can be found on my SQL Server Coding Tips page. For more Dynamics GP code, check out my GP Reports page.

GP Reports code, SQL coding, SQL Server

Dynamics Month at Packt Publishing

18 Responses to “Getting the name of a month from the month number in SQL Server”

Aatif Shabbir Reply August 8, 2012 at 6:55 am

DATENAME(month,f.CreatedDate)

LikeLike
- Victoria Yudin Reply August 8, 2012 at 7:13 am
  
  Thanks Aatif,
  
  That certainly is easier if you have an actual date. However, the views I was pulling data from simply have the number of the month, so I was converting them to a date first, then using DATENAME to get the name of the month.
  
  -Victoria
  
  LikeLike
Vaidy Reply May 24, 2011 at 3:42 pm

All I want is some reusable code which will give me name of a month number, that is going to be ALWAYS between 1 and 12. Why not having a user-defined function that does that trick? And pass the parameter as MONTH([date_value])?

LikeLike
Vaidy Reply May 24, 2011 at 3:38 pm

I thought I would write my own SQL function (something like fnMonthName) with case statement. 🙂 That way, it’s much faster and easier for me to use it from any other procedure or view. All we have to do is to keep it available from all databases.

LikeLike
Siva Reply May 23, 2011 at 10:14 am

Victoria, I had posted this sometime back last year and finally got hold of the link of this article. 🙂
http://msdynamicstips.com/2010/01/20/sql-script-for-gl-period-balances/

LikeLike
Siva Reply May 23, 2011 at 9:51 am

Victoria, You can pass the Period Start Date into this function and that will return the name of the month for that date.
We also have similar functions to get the week number and the quarter number. 🙂

LikeLike
- Victoria Yudin Reply May 24, 2011 at 7:21 am
  
  Siva,
  
  Another good idea, thanks. I thought about doing this, but the customer that this report was for is on a calendar fiscal year (as are most of our customers), so I didn’t want to add another table to the report when there was really no need for it.
  
  -Victoria
  
  LikeLike
Siva Reply May 23, 2011 at 8:07 am

Victoria, I guess we can also use a date to get the monthname using the {fn MONTHNAME()} function in SQL Server.

LikeLike
- Victoria Yudin Reply May 24, 2011 at 7:14 am
  
  Thanks Siva,
  
  This is what I was originally looking for, but could not find it, instead found lots of different select statements that were even more complicated than what I have. I simplified them to come up with the workaround here. Next time I have a question like this I am emailing you before scouring the internet for it. 😉 Do you know if using a function in this case is better for performance?
  
  -Victoria
  
  LikeLike
  - Siva Reply May 24, 2011 at 7:29 am
    
    Victoria, if the client is having calendar month, you need to build a date based on the Period ID column in the summary table and then pass that date into the function to get the month name. 🙂 Looking at that, I guess it wont be much of a weight added to the query by adding the fiscal period master table to it (which will ideally contain 12 records based on the fiscal period setup and the query I had defined in my blog). 🙂
    So either way it should be fine… 🙂 What do you think?
    
    Regarding performance, I have used this function in a view which queries about 8-10 million records and I did not see the performance going down because of the usage of that function. It is pretty efficient. 🙂
    
    LikeLike
    - Victoria Yudin Reply May 24, 2011 at 7:32 am
      
      Siva,
      
      It sounds like six of one, half dozen of another…either way should be fine. I tried to do some searching on whether in general using functions was better for performance, but did not find anything definitive. It most likely depends on the specific function and the data set.
      
      Thanks again,
      -Victoria
      
      LikeLike
      - Siva Reply May 24, 2011 at 7:38 am
        
        True.
        
        LikeLike
  - Siva Reply May 24, 2011 at 7:31 am
    
    Lolol. 😉 You have been a mentor for me in building various queries and posting them on my blog for the community folks. I will be glad to help any time (if I can). 😉
    
    LikeLike
Vaidy Reply May 23, 2011 at 7:41 am

Awesome. Thanks so much for this information. I wanted to show month name in a SSRS report. We have a function in SSRS, but I thought I would convert the number in SQL itself (before even it comes to SSRS). This will help me out. Thanks again.

LikeLike
- Victoria Yudin Reply May 24, 2011 at 12:41 pm
  
  Vaidy,
  
  Yes, I like to do that when I can, also. That way the SQL code is “self-contained” and I can use it anywhere I want. 🙂
  
  -Victoria
  
  LikeLike