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.

Bergman

Answer 1

A little hacky but should work:

SELECT DATENAME(month, DATEADD(month, @mydate-1, CAST('2008-01-01' AS datetime)))

Eiffel

Why the '-1'? Is that needed because the months in SQL Server are offset by one? – Beutler

@Beutler it's actually because the date that's being used to seed the dateadd function starts at 1. if we need January's datename, we would be adding 1 month to 2008-01-01, which would give us 2008-02-01, which is February. so we subtract 1 to account for this, and we get January again. – Ardyce

To get around the problem of subtracting 1 from your datetime, use a datetime in December as opposed to January. For example SELECT DATENAME(month, DATEADD(month, @mydate, CAST('1978-12-01' AS datetime))) – Kinakinabalu

This is a good piece of information, but fails to actually answer the question of how to convert a month number to a month name (Rather answers how to get a month name from a date). You've assumed that he has the datetime value rather than just the month number; to get this to work you now need to 'invent' a date/time value. Think the solution from leoinfo was a bit more relevant – Jade

Answer 2

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 ) )

Maurinemaurise

for readability purposes I would actually write it like this: Select DateName( month , DateAdd( month , @MonthNumber - 1 , '1900-01-01' ) ) – Bitterroot

one possible alternative solution Select DateName( month , DateAdd( month , @MonthNumber , -1 )) – Aime

That's perfect. This should be the answer. – Spanish

The simpler the better! – Trochilus

Answer 3

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

Apollinaire

Its is getting month name by date not month number, as SO asked. – Rhonda

How is this getting up votes? – Moersch

Answer 4

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

Dorian

I like this very left-field alternative way of thinkng! Food for thought – Floeter

And it's deterministic! Can be used as computed column too, thanks! – Peyton

nice... i was looking for some simple code to get months from jan to [#] and this worked great. for displaying several months, just change to something like this>> SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ',0, (@intMonth * 4)) – Croydon

I definitely wouldn't think of this as the "right way," but it's a fun way that could be used to solve other problems. – Gritty

This saved me a tonne of time. Thank you! – Fowle

Answer 5

Use the Best way

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

Aime

Answer 6

It is very simple.

select DATENAME(month, getdate())

output : January

Taster

This only works if you have a full date value, not a month integer. – Adamsite

This is not an answer to the question. He is asking how to implement a function like MonthName(1). – Doubletalk

How is GETDATE() an integer representing a month? Why is this being upvoted? – Moersch

Answer 7

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

Gritty

This is what I am looking for. Thank you for the solution. – Laughry

It's probably not the most efficient, but it's probably the most easily readable. – Gritty

It even supports localization!.. sweet! – Chara

@RosdiKasim I've been bit too many times by hard-coded manual date formatting with different localizations. – Gritty

Answer 8

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

Miguelinamiguelita

Answer 9

in addition to original

SELECT DATENAME(m, str(2) + '/1/2011')

you can do this

SELECT DATENAME(m, str([column_name]) + '/1/2011')

this way you get names for all rows in a table. where [column_name] represents a integer column containing numeric value 1 through 12

2 represents any integer, by contact string i created a date where i can extract the month. '/1/2011' can be any date

if you want to do this with variable

DECLARE @integer int;

SET @integer = 6;

SELECT DATENAME(m, str(@integer) + '/1/2011')

Spearwort

Answer 10

The following works for me:

CAST(GETDATE() AS CHAR(3))

Famished

How is GETDATE() a integer representing a month? – Moersch

Answer 11

In some locales like Hebrew, there are leap months dependant upon the year so to avoid errors in such locales you might consider the following solution:

SELECT DATENAME(month, STR(YEAR(GETDATE()), 4) + REPLACE(STR(@month, 2), ' ', '0') + '01')     

Barrelchested

Is there a function to convert a date to a Jewish date in SQL? Not that I know of... – Rogelioroger

function to convert to Jewish date: blogs.microsoft.co.il/gerireshef/2011/03/29/… – Bethina

Answer 12

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

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

Edger

Unless I'm mistaken this is not using an integer, as the OP asked. – Synsepalous

How is GETDATE() an integer representing a month? Why is this being upvoted? – Moersch

Answer 13

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()))

Beaston

So 9 would be the month as an integer? – Moersch

Yes, 9 represents the desired month as an integer. If you use the integer "4", the line of code returns "April". – Beaston

Answer 14

Sure this will work

select datename(M,GETDATE())

Hendley

SELECT datename(month, '2017/08/25') AS month_name; – Giro

How is GETDATE() an integer representing a month? – Moersch

Answer 15

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

Kovacs

Answer 16

To convert month number to month name, try the below

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

Madelaine

Answer 17

You can use the convert functin as below

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

Poly

How is GETDATE() an integer representing a month? – Moersch

Answer 18

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

SELECT DATENAME(month ,GETDATE())

Dicarlo

How is GETDATE() an integer representing a month? – Moersch

Answer 19

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

Gammadion

Answer 20

This one worked for me:

@MetricMonthNumber (some number)

SELECT 
(DateName( month , DateAdd( month , @MetricMonthNumber - 1 , '1900-01-01' ) )) AS MetricMonthName
FROM TableName

From a post above from @leoinfo and @Valentino Vranken. Just did a quick select and it works.

Sondrasone

Answer 21

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

Perez

Answer 22

select monthname(curdate());

OR

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

Ruckus

How is GETDATE() an integer representing a month? – Moersch

Answer 23

Working for me

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

Sanjuana

syntax error. plus <fieldname> cannot be a date or datetime. It must be an integer. Which would result in Jan – Moersch

Answer 24

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                   |

Vachill

You can find documentation in here. w3resource.com/mysql/date-and-time-functions/… – Vachill

the OP asked for sql-server not mysql. – Slipknot

Answer 25

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

Injun

Would use a case statement. But at least this is using month as an integer – Moersch

Answer 26

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

Eugeniusz

Answer 27

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]

Kolo

Inline table valued function plz? – Moersch

Answer 28

This is what I use:

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

Tongs

Answer 29

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

Coincide

Answer 30

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;

Are

This looks like it's referring to Oracle. The post is tagged with SQL 2005. – Llewellyn

Answer 31

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.

Greybeard

How is @date an integer representing a month? – Moersch

Answer 32

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

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

The answer is - April

Brio

Answer 33

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.

Significative

Answer 34

Use this statement

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

this will convert the month number to month full string

Tecu

TO_CHAR() is an Oracle function. The OP is using SQL Server, which has the different but equivalent CONVERT function. – Grenadine