SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012

List Month Names using SQL Functions

SQL developers may need to list month names using SQL functions during their T-SQL programming tasks. This Transact-SQL tutorial will demonstrate sql codes with DATENAME() function that can be used in order to list names of months in SQL.

SQL Server DATENAME() function with date part parameter equal to MONTH or MM value returns the name of month of an input datetime variable.

Below sample SQL codes use the SQL DateName() function with input datetime variables beginning from January to December in an arbitrary year. To generate 12 months in a single SELECT statement, I use a tally table (or SQL numbers table) which will return values between 1 and 12. The tally table that I use in the sample SQL scripts are master..spt_values but you can also refer to article Create Number Table in SQL Server

I create a date variable in the format YYYY-MM-DD by concatenating date parts like year, month and day at the step " '1900-' + CAST(number as varchar(2)) + '-1' "
Then this new date value is passed as input datetime variable for the SQL DATENAME() function. TSQL datename() function with Month datepart parameter returns month name of the input date value.

 DATENAME(MONTH, '1900-' + CAST(number as varchar(2)) + '-1') monthname
FROM master..spt_values
WHERE Type = 'P' and number between 1 and 12

Here is the output of the above Transact-SQL Select statement which list month names in a year

list month names using SQL functions

A very similar method which creates the same month list in SQL is shown below. The only difference is in step where the datename() function input date parameter value is generated from its year, month and day parts. SQL string concatenation code " '2012' + dbo.udfLeftSQLPadding(number,2,'0') + '01' " forms a date value where the udfLeftSQLPadding() user function is used for padding zeros in SQL Server

 DATENAME(MONTH, '2012' + dbo.udfLeftSQLPadding(number,2,'0') + '01') monthname
FROM master..spt_values
WHERE Type = 'P' and number between 1 and 12

The output of the above SQL Select statement is same as previous version

After building the SQL Select statement we can now create function and move these codes into it for later use in our SQL developments.

RETURNS @month TABLE (monthid smallint, monthname nvarchar(20))

 DATENAME(MM, '2012' + dbo.udfLeftSQLPadding(number,2,'0') + '01')
FROM master..spt_values
WHERE Type = 'P' and number between 1 and 12



Any SQL programmer can call SQL Server user function dbo.ListMonthNames() in order to list month number and month name. Especially if you create SQL pivot table query with month names as column names, then in your dynamic pivot table query you can use this sql function for the list of month names

SELECT * from dbo.ListMonthNames()

You will see an example where ListMonthNames() user function is used in a SQL dynamic pivot table query in SQL Server Pivot Table Tutorial.

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums

Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems