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.
This question is related to
sql
sql-server
tsql
sql-server-2005
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')
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..)
Sure this will work
select datename(M,GETDATE())
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')
i think this is enough to get month name when u have date.
SELECT DATENAME(month ,GETDATE())
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.
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.
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 |
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.
SELECT DATENAME(MONTH,dateadd(month, -3,getdate()))
select monthname(curdate());
OR
select monthname('2013-12-12');
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]
It is very simple.
select DATENAME(month, getdate())
output : January
Try this: SELECT MONTHNAME(concat('1970-',[Month int val],'-01'))
For example- SELECT MONTHNAME(concat('1970-',4,'-01'))
The answer is - April
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()))
Try this: SELECT MONTHNAME(concat('1970-',[Month int val],'-01'))
For example- SELECT MONTHNAME(concat('1970-',4,'-01'))
The answer is - April
SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (@intMonth * 4) - 3, 3)
i think this is enough to get month name when u have date.
SELECT DATENAME(month ,GETDATE())
SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (@intMonth * 4) - 3, 3)
Declare @MonthNumber int
SET @MonthNumber=DatePart(Month,GETDATE())
Select DateName( month , DateAdd( month , @MonthNumber , 0 ) - 1 )
Explaination:
MonthNumberDatePart which Return Month NumberI 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 ) )
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;
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 ) )
SELECT DATENAME(month, GETDATE()) AS 'Month Name'
You can use the convert functin as below
CONVERT(VARCHAR(3), DATENAME(MM, GETDATE()), 100)
SELECT DATENAME(MONTH,dateadd(month, -3,getdate()))
Working for me
SELECT MONTHNAME(<fieldname>) AS "Month Name" FROM <tablename> WHERE <condition>
Use the Best way
Select DateName( month , DateAdd( month , @MonthNumber , -1 ))
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]
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
Use this statement to convert Month numeric value to Month name.
SELECT CONVERT(CHAR(3), DATENAME(MONTH, GETDATE()))
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..)
SELECT DateName(M, DateAdd(M, @MONTHNUMBER, -1))
select monthname(curdate());
OR
select monthname('2013-12-12');
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
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 |
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.
Working for me
SELECT MONTHNAME(<fieldname>) AS "Month Name" FROM <tablename> WHERE <condition>
Declare @MonthNumber int
SET @MonthNumber=DatePart(Month,GETDATE())
Select DateName( month , DateAdd( month , @MonthNumber , 0 ) - 1 )
Explaination:
MonthNumberDatePart which Return Month NumberI 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 ) )
Sure this will work
select datename(M,GETDATE())
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
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')
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()))
The following works for me:
CAST(GETDATE() AS CHAR(3))
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.
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
Use this statement to convert Month numeric value to Month name.
SELECT CONVERT(CHAR(3), DATENAME(MONTH, GETDATE()))
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')
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 ) )
You can use the convert functin as below
CONVERT(VARCHAR(3), DATENAME(MM, GETDATE()), 100)
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')
Use this statement
SELECT TO_CHAR(current_date,'dd MONTH yyyy') FROM dual
this will convert the month number to month full string
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;
Use this statement
SELECT TO_CHAR(current_date,'dd MONTH yyyy') FROM dual
this will convert the month number to month full string
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')
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
The following works for me:
CAST(GETDATE() AS CHAR(3))
To convert month number to month name, try the below
declare @month smallint = 1
select DateName(mm,DATEADD(mm,@month - 1,0))
SELECT DateName(M, DateAdd(M, @MONTHNUMBER, -1))
To convert month number to month name, try the below
declare @month smallint = 1
select DateName(mm,DATEADD(mm,@month - 1,0))
Use the Best way
Select DateName( month , DateAdd( month , @MonthNumber , -1 ))
SELECT DATENAME(month, GETDATE()) AS 'Month Name'
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
It is very simple.
select DATENAME(month, getdate())
output : January
Source: Stackoverflow.com