Convert Month Number to Month Name Function in SQL

Question

I have months stored in SQL Server as 1,2,3,4,...12. I would like to display them as January,February etc. Is there a function in SQL Server like MonthName(1) = January? I am trying to avoid a CASE statement, if possible.

Answer 1

Declare @MonthNumber int
SET @MonthNumber=DatePart(Month,GETDATE())
Select DateName( month , DateAdd( month , @MonthNumber , 0 ) - 1 )

Explaination:

1. First Decalre Variable MonthNumber
2. Get Current Month for DatePart which Return Month Number
3. Third Query Return Month Name

Answer 2

It is very simple.

select DATENAME(month, getdate())

output : January

Answer 3

The following works for me:

CAST(GETDATE() AS CHAR(3))

Answer 4

I think this is the best way to get the month name when you have the month number

Select DateName( month , DateAdd( month , @MonthNumber , 0 ) - 1 )

Or

Select DateName( month , DateAdd( month , @MonthNumber , -1 ) )

Answer 5

You can create a function like this to generate the Month and do SELECT dbo.fn_GetMonthFromDate(date_column) as Month FROM table_name

/****** Object:  UserDefinedFunction [dbo].[fn_GetMonthFromDate]    Script Date: 11/16/2018 10:26:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetMonthFromDate] 
(@date datetime)
RETURNS varchar(50)
AS
BEGIN
    DECLARE @monthPart int


SET @monthPart = MONTH(@date)

IF @monthPart = 1
    BEGIN
        RETURN 'January'
    END
ELSE IF @monthPart = 2
    BEGIN
        RETURN 'February'
    END
ELSE IF @monthPart = 3
    BEGIN
        RETURN 'March'
    END
ELSE IF @monthPart = 4
    BEGIN
        RETURN 'April'
    END
ELSE IF @monthPart = 5
    BEGIN
        RETURN 'May'
    END
ELSE IF @monthPart = 6
    BEGIN
        RETURN 'June'
    END
ELSE IF @monthPart = 7
    BEGIN
        RETURN 'July'
    END
ELSE IF @monthPart = 8
    BEGIN
        RETURN 'August'
    END
ELSE IF @monthPart = 9
    BEGIN
        RETURN 'September'
    END
ELSE IF @monthPart = 10
    BEGIN
        RETURN 'October'
    END
ELSE IF @monthPart = 11
    BEGIN
        RETURN 'November'
    END
ELSE IF @monthPart = 12
    BEGIN
        RETURN 'December'
    END
RETURN NULL END

Answer 6

There is no system defined function in SQL server. But you can create your own user-defined function- a scalar function. You would find scalar functions in the Object Explorer for your database: Programmability->Functions->Scalar-valued Functions. Below, I use a table variable to bring it all together.

--Create the user-defined function
CREATE FUNCTION getmonth (@num int)
RETURNS varchar(9) --since 'September' is the longest string, length 9
AS
BEGIN

DECLARE @intMonth Table (num int PRIMARY KEY IDENTITY(1,1), month varchar(9))

INSERT INTO @intMonth VALUES ('January'), ('February'), ('March'), ('April'), ('May')
                           , ('June'), ('July'), ('August') ,('September'), ('October')
                           , ('November'), ('December')

RETURN (SELECT I.month
        FROM @intMonth I
        WHERE I.num = @num)
END
GO

--Use the function for various months
SELECT dbo.getmonth(4) AS [Month]
SELECT dbo.getmonth(5) AS [Month]
SELECT dbo.getmonth(6) AS [Month]