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


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.

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

  1. DATENAME(month,f.CreatedDate)

    Like

    • 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

      Like

  2. 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])?

    Like

  3. 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.

    Like

  4. 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/

    Like

  5. 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. 🙂

    Like

    • 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

      Like

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

    Like

    • 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

      Like

      • 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. 🙂

        Like

        • 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

          Like

      • 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). 😉

        Like

  7. 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.

    Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | Getting the name of a month from the month number in SQL Server | Victoria YudinGetting the name of a month from the month number in SQL Server | Victoria Yudin - DynamicAccounting.net - May 3, 2016

    […] shows us how to Get the name of a month from the month number in SQL Server. This is very handing for reporting from Dynamics […]

    Like

  2. Getting the name of a month from the month number in SQL Server | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - May 24, 2011

    […] 0 Victoria shows us how to Get the name of a month from the month number in SQL Server. This is very handing for reporting from Dynamics […]

    Like

  3. Getting the name of a month from the month number in SQL Server | Interesting Findings & Knowledge Sharing - May 23, 2011

    […] the rest here: Getting the name of a month from the month number in SQL Server Posted in Blogs I Follow, Blogsphere – I, Victoria Yudin | Tags: check-failed, dynamics, […]

    Like

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

%d bloggers like this: