Covert number month to name of the month

Question

How do i convert my column with the number of the months to the names of the months?

For example:

I have the month column and it has 7,7,8,9 however i want it to display as July, July, August, September.

Please add some code examples of what you did you try till now. — Yan, May 15 '18 at 09:37
Does the column hold comma separated values like `7,7,8,9` ? — Mazhar, May 15 '18 at 09:43
If the answer @EzLo linked to doesn't give the result you are expecting, what is it giving? — Thom A, May 15 '18 at 09:49

DxTx · Answer 1 · 2018-05-15T09:53:23.220

0

Try this one...

DECLARE @MonthNumber int
SET @MonthNumber = 1

SELECT DATENAME(MONTH, DATEADD(MONTH, @MonthNumber, '2017/12/31')) AS 'Month'

Or

SELECT DATENAME(MONTH, DATEADD(MONTH, yourColumnHere, '2017/12/31')) AS 'Month'

edited May 15 '18 at 09:53

answered May 15 '18 at 09:43

DxTx

3,049
3
23
34

1

The second argument to [`datename`](https://learn.microsoft.com/en-us/sql/t-sql/functions/datename-transact-sql?view=sql-server-2017) is a date, not an integer – Andomar May 15 '18 at 09:44
@Andomar, Thanks. Updated. – DxTx May 15 '18 at 09:53

score 0 · Answer 2 · answered May 15 '18 at 09:55

If the column holds a CSV of month numbers then try this (SQL_Server 2016)

DECLARE @t TABLE (
    Months_csv  NVARCHAR (15)
);
INSERT INTO @t
(
    Months_csv
)
VALUES
(
    N'7,7,8,9'
);

SELECT
    value
    ,[MonthName]=DATENAME(MONTH, DATEADD(MONTH, CAST(value AS INT), -1))
FROM
    @t  T
CROSS APPLY
    STRING_SPLIT(T.Months_csv, ',');

Output

value   MonthName
7       July
7       July
8       August
9       September

If it isn't a CSV list then try this

DECLARE @t TABLE (
    Months_ int
);
INSERT INTO @t
(
    Months_
)
VALUES
(7),(7),(8),(9);

SELECT
    T.Months_
    ,[MonthName]=DATENAME(MONTH, DATEADD(MONTH, T.Months_, -1))
FROM
    @t  T

Output

Months_ MonthName
7       July
7       July
8       August
9       September

score 0 · Answer 3 · answered May 15 '18 at 10:00

Use this Udf

CREATE FUNCTION [dbo].[udf_GetMonthNAME]
(
@string nvarchar(max)
)
RETURNS  @OutTable TABLE
(
MonthNames nvarchar(max)
)
AS
BEGIN

    DECLARE @Temp AS TABLE
    (
    MonthNumber nvarchar(max)
    )
    INSERT INTO @Temp
    SELECT @string


    INSERT INTO @OutTable(MonthNames)
    SELECT STUFF((SELECT ', '+MonthNumber 
        FROM
        (
        SELECT DATENAME(MONTH,DATEADD(MONTH,MonthNumber-5,Getdate())) AS MonthNumber
        FROM
        (
        SELECT CAST(Split.a.value('.','nvarchar(max)') AS INT) AS MonthNumber
        FROM
        (
        SELECT CAST('<S>'+REPLACE(MonthNumber,',','</S><S>')+'</S>' AS XML ) AS MonthNumber
        FROM @Temp
        )AS A
        CROSS APPLY MonthNumber.nodes('S') AS Split(a)
        )fn
        )fnl FOR XML PATH ('')),1,1,'')


RETURN
END

SELECT * FROM [dbo].[udf_GetMonthNAME] ('7,7,8,9')
GO

Result

MonthNames
--------------
 July, July, August, September

Covert number month to name of the month

3 Answers3