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 expression, if possible.

Answer 1

SQL Server nowadays can get the ordinalnummer of a comma separated value of values, using the STRING_SPLIT function

Also the names of the month are already stored in SQL Server (use: SELECT month FROM sys.syslanguages)

Converting the number of the month to the name of the month can be done by joining to this result

select value, ordinal 
from sys.syslanguages 
cross apply string_split(months,',',1) 
where name='Nederlands'

output:

value	ordinal
januari	1
februari	2
maart	3
april	4
mei	5
juni	6
juli	7
augustus	8
september	9
oktober	10
november	11
december	12

see, for other languages than Dutch: DBFIDDLE

Answer 2

This is what I use:

SELECT TRIM(SUBSTRING('January  February March    April    May      June     July     August   SeptemberOctober  November December ', @MonthNumber * 9 - 8,9))

Answer 3

If anyone is trying to get the same kind of thing in MySQL. please check below query.

 SELECT MONTH(STR_TO_DATE('November', '%M'))

By this I got required result.

Answer 4

Try this: SELECT MONTHNAME(concat('1970-',[Month int val],'-01'))

For example- SELECT MONTHNAME(concat('1970-',4,'-01'))

The answer is - April

Answer 5

Starting with SQL Server 2012, you can use FORMAT and DATEFROMPARTS to solve this problem. (If you want month names from other cultures, change: en-US)

select FORMAT(DATEFROMPARTS(1900, @month_num, 1), 'MMMM', 'en-US')

If you want a three-letter month:

select FORMAT(DATEFROMPARTS(1900, @month_num, 1), 'MMM', 'en-US')

If you really want to, you can create a function for this:

CREATE FUNCTION fn_month_num_to_name
(
    @month_num tinyint
)
RETURNS varchar(20)
AS
BEGIN
    RETURN FORMAT(DATEFROMPARTS(1900, @month_num, 1), 'MMMM', 'en-US')
END

Answer 6

The following works for me:

CAST(GETDATE() AS CHAR(3))

Answer 7

SELECT DATENAME(MONTH,dateadd(month, -3,getdate()))

Answer 8

Sure this will work

select datename(M,GETDATE())

Answer 9

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 10

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 11

you can get the date like this. eg:- Users table

id name created_at
1  abc  2017-09-16
2  xyz  2017-06-10

you can get the monthname like this

select year(created_at), monthname(created_at) from users;

output

+-----------+-------------------------------+
| year(created_at) | monthname(created_at)  |
+-----------+-------------------------------+
|      2017        | september              |
|      2017        | june                   |

Answer 12

To convert month number to month name, try the below

declare @month smallint = 1
select DateName(mm,DATEADD(mm,@month - 1,0))

Answer 13

It is very simple.

select DATENAME(month, getdate())

output : January

Answer 14

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]

Answer 15

SELECT DateName(M, DateAdd(M, @MONTHNUMBER, -1))

Answer 16

Just subtract the current month from today's date, then add back your month number. Then use the datename function to give the full name all in 1 line.

print datename(month,dateadd(month,-month(getdate()) + 9,getdate()))

Answer 17

Use the Best way

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

Answer 18

Here is my solution using some information from others to solve a problem.

datename(month,dateadd(month,datepart(month,Help_HelpMain.Ticket_Closed_Date),-1)) as monthname

Answer 19

Use this statement for getting month name:

DECLARE @date datetime
SET @date='2015/1/4 00:00:00'

SELECT CAST(DATENAME(month,@date )  AS CHAR(3))AS 'Month Name'

This will give you short month name. Like this: Jan, Feb, Mar, etc.

Answer 20

Use this statement to convert Month numeric value to Month name.

SELECT CONVERT(CHAR(3), DATENAME(MONTH, GETDATE()))

Answer 21

Working for me

SELECT MONTHNAME(<fieldname>) AS "Month Name" FROM <tablename> WHERE <condition>

Answer 22

select monthname(curdate());

OR

select monthname('2013-12-12');

Answer 23

to_char(to_date(V_MONTH_NUM,'MM'),'MONTH')

where V_MONTH_NUM is the month number

SELECT to_char(to_date(V_MONTH_NUM,'MM'),'MONTH')  from dual;

Answer 24

i think this is enough to get month name when u have date.

SELECT DATENAME(month ,GETDATE())

Answer 25

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 26

SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (@intMonth * 4) - 3, 3)

Answer 27

Use this statement

SELECT TO_CHAR(current_date,'dd MONTH yyyy') FROM dual

this will convert the month number to month full string

Answer 28

SELECT DATENAME(month, GETDATE()) AS 'Month Name'

Answer 29

You can use the convert functin as below

CONVERT(VARCHAR(3), DATENAME(MM, GETDATE()), 100)

Answer 30

You can use the inbuilt CONVERT function

select CONVERT(varchar(3), Date, 100)  as Month from MyTable.

This will display first 3 characters of month (JAN,FEB etc..)