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.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s