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.

score 326 · Answer 1 · edited May 15 '18 at 15:14

326

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

edited May 15 '18 at 15:14

Robert Harvey

178,213
47
333
501

answered Oct 09 '08 at 17:48

leoinfo

7,860
8
36
48

25

for readability purposes I would actually write it like this: Select DateName( month , DateAdd( month , @MonthNumber - 1 , '1900-01-01' ) ) – Valentino Vranken Jun 29 '10 at 06:50
11

one possible alternative solution Select DateName( month , DateAdd( month , @MonthNumber , -1 )) – Asif Aug 13 '13 at 09:40
5

That's perfect. This should be the answer. – gotqn Oct 08 '13 at 13:35
The simpler the better! – mrddr Apr 21 '21 at 11:26

score 173 · Accepted Answer · answered Oct 09 '08 at 01:03

173

A little hacky but should work:

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

answered Oct 09 '08 at 01:03

Alexander Kojevnikov

17,580
5
49
46

2

Why the '-1'? Is that needed because the months in SQL Server are offset by one? – DoomerDGR8 Nov 01 '16 at 08:40
2

@DoomerDGR8 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. – DForck42 Dec 05 '16 at 16:11
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))) – Steve Matthews Sep 01 '17 at 13:26
6

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 – schizoid04 Jun 04 '18 at 19:51

score 109 · Answer 3 · edited Nov 21 '11 at 21:05

109

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

edited Nov 21 '11 at 21:05

Marcus

12,296
5
48
66

answered Jun 29 '10 at 06:36

Dharamvir

1,131
1
7
2

5

Its is getting month name by date not month number, as SO asked. – Imad Apr 17 '15 at 07:07
How is this getting up votes? – TamusJRoyce Dec 30 '22 at 05:23

score 85 · Answer 4 · edited Nov 21 '11 at 21:06

85

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

edited Nov 21 '11 at 21:06

Marcus

12,296
5
48
66

answered Nov 21 '11 at 20:58

Darryl Martin

851
6
2

12

I like this very left-field alternative way of thinkng! Food for thought – Michael Rodrigues Feb 22 '12 at 04:28
4

And it's deterministic! Can be used as computed column too, thanks! – Irawan Soetomo Jul 13 '12 at 07:37
1

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)) – Pablo Contreras Mar 26 '15 at 17:46
2

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. – Paul Mar 23 '18 at 14:18

Asif · Answer 5 · 2015-09-18T09:50:19.540

32

Use the Best way

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

edited Sep 18 '15 at 09:50

answered Aug 13 '13 at 09:36

Asif

2,657
19
25

score 20 · Answer 6 · answered Jan 26 '17 at 14:40

20

It is very simple.

select DATENAME(month, getdate())

output : January

answered Jan 26 '17 at 14:40

Saeed ur Rehman

727
2
10
25

6

This only works if you have a full date value, not a month integer. – gunr2171 May 19 '17 at 13:40
3

This is not an answer to the question. He is asking how to implement a function like MonthName(1). – amuliar Mar 30 '18 at 08:08
How is GETDATE() an integer representing a month? Why is this being upvoted? – TamusJRoyce Dec 30 '22 at 05:15

Paul · Answer 7 · 2019-01-08T23:12:43.073

17

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

edited Jan 08 '19 at 23:12

answered Mar 23 '18 at 16:48

Paul

3,634
1
18
23

1

This is what I am looking for. Thank you for the solution. – Abdullah Al Mamun Dec 23 '18 at 14:19
It's probably not the most efficient, but it's probably the most easily readable. – Paul Jan 08 '19 at 23:01
1

It even supports localization!.. sweet! – Rosdi Kasim Apr 25 '19 at 08:05
@RosdiKasim I've been bit too many times by hard-coded manual date formatting with different localizations. – Paul Jan 28 '22 at 17:24

score 9 · Answer 8 · edited Nov 21 '11 at 21:05

9

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

edited Nov 21 '11 at 21:05

Marcus

12,296
5
48
66

answered Mar 09 '09 at 12:27

score 7 · Answer 9 · edited Dec 19 '18 at 00:23

7

The following works for me:

CAST(GETDATE() AS CHAR(3))

edited Dec 19 '18 at 00:23

Ilyes

14,640
4
29
55

answered Feb 26 '13 at 12:17

unitario

6,295
4
30
43

How is GETDATE() a integer representing a month? – TamusJRoyce Dec 30 '22 at 05:12

score 7 · Answer 10 · 2011-09-20T15:11:57.157

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

score 6 · Answer 11 · answered Oct 09 '08 at 01:15

6

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

answered Oct 09 '08 at 01:15

Jim Burger

4,399
1
24
27

1

Is there a function to convert a date to a Jewish date in SQL? Not that I know of... – Hila DG Oct 20 '16 at 03:45
function to convert to Jewish date: http://blogs.microsoft.co.il/gerireshef/2011/03/29/%D7%94%D7%9C%D7%95%D7%97-%D7%94%D7%A2%D7%91%D7%A8%D7%99-%D7%91%D7%A2%D7%96%D7%A8%D7%AA-tsql/ – AJ AJ Oct 23 '18 at 10:08

score 5 · Answer 12 · answered Feb 23 '15 at 10:09

5

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

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

answered Feb 23 '15 at 10:09

Ashish Singh

275
3
5

Unless I'm mistaken this is not using an integer, as the OP asked. – influent Nov 30 '16 at 19:22
How is GETDATE() an integer representing a month? Why is this being upvoted? – TamusJRoyce Dec 30 '22 at 05:19

score 5 · Answer 13 · answered Sep 29 '15 at 21:41

5

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

answered Sep 29 '15 at 21:41

Geoffrey Fuller

186
1
6

So 9 would be the month as an integer? – TamusJRoyce Dec 30 '22 at 05:18
1

Yes, 9 represents the desired month as an integer. If you use the integer "4", the line of code returns "April". – Geoffrey Fuller Feb 04 '23 at 01:59

score 4 · Answer 14 · edited Dec 19 '18 at 00:22

4

Sure this will work

select datename(M,GETDATE())

edited Dec 19 '18 at 00:22

Ilyes

14,640
4
29
55

answered Jul 05 '14 at 09:55

Shyam Sa

331
4
8

1

SELECT datename(month, '2017/08/25') AS month_name; – Tokci Jan 10 '22 at 12:00
How is GETDATE() an integer representing a month? – TamusJRoyce Dec 30 '22 at 05:13

score 3 · Answer 15 · edited Apr 28 '16 at 21:12

3

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

edited Apr 28 '16 at 21:12

Matt S

14,976
6
57
76

answered Apr 28 '16 at 20:47

Isaiah

162
2
5

score 3 · Answer 16 · answered Mar 06 '17 at 12:46

3

To convert month number to month name, try the below

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

answered Mar 06 '17 at 12:46

M2012

3,465
2
20
27

score 2 · Answer 17 · edited Oct 04 '12 at 09:33

2

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

SELECT DATENAME(month ,GETDATE())

edited Oct 04 '12 at 09:33

fancyPants

50,732
33
89
96

answered Oct 04 '12 at 09:25

Benazir

63
1

How is GETDATE() an integer representing a month? – TamusJRoyce Dec 30 '22 at 05:21

score 2 · Answer 18 · edited Dec 19 '18 at 00:22

2

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

edited Dec 19 '18 at 00:22

Ilyes

14,640
4
29
55

answered May 09 '13 at 13:45

gvila

29
1

score 2 · Answer 19 · edited Nov 21 '11 at 21:05

2

You can use the convert functin as below

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

edited Nov 21 '11 at 21:05

Marcus

12,296
5
48
66

answered Nov 09 '10 at 00:36

Nori

53
1

How is GETDATE() an integer representing a month? – TamusJRoyce Dec 30 '22 at 05:23

score 1 · Answer 20 · edited Jul 20 '12 at 07:20

1

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

Explaination:

First Decalre Variable MonthNumber
Get Current Month for DatePart which Return Month Number
Third Query Return Month Name

edited Jul 20 '12 at 07:20

Tisho

8,320
6
44
52

answered Jul 20 '12 at 07:05

Wafa Abbas

11
1

score 1 · Answer 21 · edited Dec 14 '13 at 05:40

1

select monthname(curdate());

OR

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

edited Dec 14 '13 at 05:40

LaurentG

11,128
9
51
66

answered Dec 14 '13 at 05:11

Piyush

29
1

How is GETDATE() an integer representing a month? – TamusJRoyce Dec 30 '22 at 05:20

score 1 · Answer 22 · edited Dec 14 '13 at 13:47

1

Working for me

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

edited Dec 14 '13 at 13:47

kleopatra

51,061
28
99
211

answered Dec 14 '13 at 13:34

Kashif Aslam

19
1

syntax error. plus cannot be a date or datetime. It must be an integer. Which would result in Jan – TamusJRoyce Dec 30 '22 at 05:20

score 1 · Answer 23 · answered Sep 22 '17 at 06:04

1

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                   |

answered Sep 22 '17 at 06:04

Janaka Pushpakumara

4,769
5
29
34

You can find documentation in here. https://www.w3resource.com/mysql/date-and-time-functions/mysql-monthname-function.php – Janaka Pushpakumara Apr 13 '18 at 09:09
the OP asked for sql-server not mysql. – tavalendo Oct 01 '18 at 10:31

score 1 · Answer 24 · answered Nov 16 '18 at 15:39

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

Would use a case statement. But at least this is using month as an integer — TamusJRoyce, Dec 30 '22 at 05:14

score 1 · Answer 25 · edited Aug 05 '11 at 23:20

1

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.

edited Aug 05 '11 at 23:20

bobs

21,844
12
67
78

answered Aug 05 '11 at 22:10

Roadrunner327

11
1

score 0 · Answer 26 · answered Jun 24 '15 at 15:58

0

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

answered Jun 24 '15 at 15:58

lancepants28

1
1

score 0 · Answer 27 · answered Dec 16 '16 at 21:58

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]

Inline table valued function plz? – TamusJRoyce Dec 30 '22 at 05:16 — TamusJRoyce, Dec 30 '22 at 05:16

score 0 · Answer 28 · answered Mar 01 '23 at 13:37

0

This is what I use:

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

answered Mar 01 '23 at 13:37

Tom Robinson

1,850
1
15
14

score -1 · Answer 29 · edited Oct 11 '12 at 09:37

-1

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;

edited Oct 11 '12 at 09:37

Brian Webster

30,033
48
152
225

answered Jul 10 '12 at 15:56

shailesh

9

This looks like it's referring to Oracle. The post is tagged with SQL 2005. – Philter Jun 03 '13 at 14:47

score -1 · Answer 30 · edited Jun 04 '15 at 06:15

-1

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.

edited Jun 04 '15 at 06:15

Indian

529
1
12
25

answered Jun 04 '15 at 05:55

user4972370

1

How is @date an integer representing a month? – TamusJRoyce Dec 30 '22 at 05:19

score -1 · Answer 31 · edited Jul 31 '20 at 14:38

-1

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

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

The answer is - April

edited Jul 31 '20 at 14:38

Ajay2707

5,690
6
40
58

answered Feb 14 '20 at 12:13

Atanu Samanta

145
2
11

score -1 · Answer 32 · answered Dec 03 '21 at 10:42

-1

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.

answered Dec 03 '21 at 10:42

CodeByAk

139
5

score -2 · Answer 33 · edited Nov 21 '11 at 21:06

-2

Use this statement

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

this will convert the month number to month full string

edited Nov 21 '11 at 21:06

Marcus

12,296
5
48
66

answered Sep 28 '11 at 12:54

Cedricve

1

5

TO_CHAR() is an Oracle function. The OP is using SQL Server, which has the different but equivalent CONVERT function. – APC Sep 28 '11 at 16:04

Convert Month Number to Month Name Function in SQL

33 Answers33

Linked

Related