List Month Names using SQL Functions on SQL Server
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
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.