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

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

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

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

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.

CREATE FUNCTION ListMonthNames()
RETURNS @month TABLE (monthid smallint, monthname nvarchar(20))
AS
BEGIN

INSERT INTO @month
SELECT
 number,
 DATENAME(MM, '2012' + dbo.udfLeftSQLPadding(number,2,'0') + '01')
FROM master..spt_values
WHERE Type = 'P' and number between 1 and 12
ORDER BY Number

RETURN

END

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 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems