Convert Month Number to Month Name Function in SQL
SqlSql ServerTsqlSql Server-2005Sql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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 ) )
Solution 2 - Sql
A little hacky but should work:
SELECT DATENAME(month, DATEADD(month, @mydate-1, CAST('2008-01-01' AS datetime)))
Solution 3 - Sql
SELECT DATENAME(month, GETDATE()) AS 'Month Name'
Solution 4 - Sql
SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (@intMonth * 4) - 3, 3)
Solution 5 - Sql
Use the Best way
Select DateName( month , DateAdd( month , @MonthNumber , -1 ))
Solution 6 - Sql
It is very simple.
select DATENAME(month, getdate())
output : January
Solution 7 - Sql
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
Solution 8 - Sql
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..)
Solution 9 - Sql
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')
Solution 10 - Sql
The following works for me:
CAST(GETDATE() AS CHAR(3))
Solution 11 - Sql
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')
Solution 12 - Sql
Use this statement to convert Month numeric value to Month name.
SELECT CONVERT(CHAR(3), DATENAME(MONTH, GETDATE()))
Solution 13 - Sql
You can use the convert functin as below
CONVERT(VARCHAR(3), DATENAME(MM, GETDATE()), 100)
Solution 14 - Sql
i think this is enough to get month name when u have date.
SELECT DATENAME(month ,GETDATE())
Solution 15 - Sql
Sure this will work
select datename(M,GETDATE())
Solution 16 - Sql
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()))
Solution 17 - Sql
SELECT DateName(M, DateAdd(M, @MONTHNUMBER, -1))
Solution 18 - Sql
To convert month number to month name, try the below
declare @month smallint = 1
select DateName(mm,DATEADD(mm,@month - 1,0))
Solution 19 - Sql
SELECT DATENAME(MONTH,dateadd(month, -3,getdate()))
Solution 20 - Sql
select monthname(curdate());
OR
select monthname('2013-12-12');
Solution 21 - Sql
Working for me
SELECT MONTHNAME(<fieldname>) AS "Month Name" FROM <tablename> WHERE <condition>
Solution 22 - Sql
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.
Solution 23 - Sql
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
DatePartwhich Return Month Number - Third Query Return Month Name
Solution 24 - Sql
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 |
Solution 25 - Sql
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.
Solution 26 - Sql
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
Solution 27 - Sql
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]
Solution 28 - Sql
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
Solution 29 - Sql
The easiest way is by calling the function MONTHNAME(your_date). your_date can be a static value or the value from one of your table fields.
Solution 30 - Sql
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;
Solution 31 - Sql
Try this: SELECT MONTHNAME(concat('1970-',[Month int val],'-01'))
For example- SELECT MONTHNAME(concat('1970-',4,'-01'))
The answer is - April
Solution 32 - Sql
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.
Solution 33 - Sql
Use this statement
SELECT TO_CHAR(current_date,'dd MONTH yyyy') FROM dual
this will convert the month number to month full string