SQL MONTHNAME() Function

The SQL MONTHNAME() is a function, and returns a string indicating the full name of the specified month of a given input date value.

The SQL MONTHNAME() function is supports only date or datetime based table columns or fields.

It can be used in SELECT statement as well in where clause.

If you use SQL Server (or any other DBMS), you may need to return your nickname for a month. In other words, it means a three-letter abbreviation for one month. For example, you need "Dez" instead of "December".

Here are four ways to extract the abbreviated month name from the SQL Server date:

Approach 1: FORMAT () function

The FORMAT () function has been available since SQL Server 2012 and is the simplest way to return the month as a three-letter abbreviation.

Here is an example of how this works.

DECLARE @ datedatetime2 = `20000101'; 
 SELECT FORMAT (@date,'MMM') AS'FORMAT'; 

Result:

Example 2: This method uses the ONLY FORMAT function (included with SQL Server 2012) to get the short name of the month And weekdays.

USE tempdb
GO
SELECT [ID]
     , [Date]
     , FORMAT([Date],'MMM') AS [Short Month Name]
     , FORMAT([Date],'ddd') AS [Short Weekday Name]
FROM tbl_Sample;

Example 3: Here’s an example:

DECLARE @date datetime2 = '2000-01-01';
SELECT CAST(DATENAME(month, @date) AS CHAR(3)) AS 'CAST/DATENAME';

Result:

Explanation:

The first line simply declares a variable and assigns a date to it. The second line is where we return the short month name from the date.

This option converts dates to CHAR(3), removing all characters after the first three.

APPROACH 2: LEFT() and DATENAME() function

In this method, we will use the DATENAME & LEFT function to get the abbreviation of the month and day of the week.

some forum and it is solved by using some functions like DATENAME and LEFT functions. However, let's solve it just using the FORMAT function.

Example 1: This option is similar to the previous option, except that it uses the LEFT() function to get the leftmost 3 characters of the date.

DECLARE @date datetime2 = '2000-01-01';
SELECT LEFT(DATENAME(month, @date), 3) AS 'LEFT/DATENAME';

Result:

Example 2: Let me create an example to explain it.

USE tempdb
GO

CREATE TABLE tbl_Sample
(
 [ID] INT,
 [Date] DATETIME
)
GO

INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-02-02')
INSERT INTO tbl_Sample VALUES (3,'2013-03-03')
INSERT INTO tbl_Sample VALUES (4,'2013-04-04')
INSERT INTO tbl_Sample VALUES (5,'2013-05-05')
INSERT INTO tbl_Sample VALUES (6,'2013-06-06')
INSERT INTO tbl_Sample VALUES (7,'2013-07-07')

The DATENAME & LEFT function to get 3 character name of month and weekday.

USE tempdb
GO

SELECT
       [ID]
     , [Date]
     , LEFT(DATENAME(MONTH,[Date]),3) AS [Short Month Name]
     , LEFT(DATENAME(WEEKDAY,[Date]),3) AS [Short Weekday Name]
FROM tbl_Sample

APPROACH 3: The LEFT() and CONVERT() Functions

This option converts the date to varchar, then takes the first three characters.

Example:

DECLARE @date datetime2 = '2000-01-01';
SELECT LEFT(CONVERT(varchar, @date, 100), 3) AS 'LEFT/CONVERT';

Result:

In this example, the one hundred argument patterns the date in order that it`s withinside the following format: mon dd yyyy hh:miAM (or PM). So in our case, the date is styled as Jan 1 2000 12:00AM.

From there, it`s surely a be counted of snipping off the primary 3 letters with the LEFT() feature.

APPROACH 4: The LEFT() and MONTHNAME() Functions

This choice makes use of the MONTHNAME() ODBC scalar feature to go back the month name. And as with the preceding examples, we surely extract the primary 3 letters of that month name.

Example:

DECLARE @date datetime2 = '2000-01-01';
SELECT LEFT({fn MONTHNAME(@date)}, 3) AS 'LEFT/MONTHNAME';

Result:

Example 1: Here is a simple way to display all months between a range of dates, without referencing any table:

SELECT
MOD( TRUNC( MONTHS_BETWEEN( '2015-07-29', '2015-03-28' ) ), 12 ) as MONTHS
FROM DUAL;

Example 2: Here is a way to name all of the months between two dates:

select to_char(which_month, 'Mon-yyyy') month
from
(
select
add_months(to_date('01-2016','mm-yyyy'), rownum-1) which_month
from
dba_objects
where rownum <= months_between(to_date('12-2016','mm-yyyy'), 
add_months(to_date('01-2016','mm-yyyy'), -1))
order by
which_month
);

Example 3: Here is a script that uses the last_day function to show the number of months between two dates:

select distinct(last_day(to_date(td.end_date + 1 - rownum)))
from all_objects,
   
(-- this is just to easily substitute dates for the example...
select to_date('30-JAN-2010') start_date
,to_date('15-MAR-2011') end_date
FROM   DUAL  ) td
where
trunc(td.end_date + 1 - rownum,'MM') >= trunc(td.start_date,'MM')
order by 1;

Example 4: Now let's collect the records between two months (assuming the months from February to August without specifying a year). Note that if we use a single month in the between statement, then for any year, the month range we specified will be returned by the query.

For example, if we request records between February and August, we will receive records February through August for all years.

SELECT * FROM `dt_tb` WHERE month (dt) BETWEEN `02' and '08' 

The above query will return records for all months from February to August of any year.

Example 5: We can specify the year also along with the months like this:

SELECT * FROM `dt_tb` WHERE MONTH(dt) 
BETWEEN '02' and '08' AND YEAR(dt) BETWEEN 2004 and 2005;

The above query may not give us the desired result as it will collect records of Feb to Aug for the years 2004 and 2005. To get the records between two months with the year we have to change this query.

Example 6: Let us say we want records from March 2015 to Feb 2016.

SELECT FROM table_name WHERE dte_field 
BETWEEN '2015-03-01' AND LAST_DAY('2016-02-01')

By using LAST_DAY function we are getting the last date of the month.

Example 7: Now let us move to select a range of records between two dates. Here is the SQL for this

SELECT * FROM `dt_tb` WHERE dt BETWEEN '2005-01-01' AND '2005-12-31';

Many a times we come across a scenario where we may need to get Month name from Date in Sql Server. In this article we will see how we can get Month name from Date in Sql Server.

APPROACH 1: Using DATENAME Function

We can use DATENAME() function to get Month name from Date in Sql Server, here we need specify datepart parameter of the DATENAME function as month or mm or m all will return the same result.

Example 1:

SELECT GETDATE() 'Today', DATENAME(month,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(mm,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(m,GETDATE()) 'Month Name'

RESULT:

Example 2: Here get the different languages for months:

--Change default langauage to Russian for this session

SET LANGUAGE Russian
SELECT DATENAME(mm, GETDATE()) 'Russian Month Name'

--Change the language back to English from Arabic

SET LANGUAGE English
SELECT DATENAME(mm, GETDATE()) 'English Month Name'

The sys.syslanguages catalog view provides the list of languages to which we can change the current sessions language using SET LANGUAGE statement.

Example 3: Here’s how to get the month name with this function:

DECLARE @date datetime2 = '2018-07-01';
SELECT DATENAME(month, @date) AS Result;

Result:

APPROACH 2: MySQL MONTHNAME function

The MySQL MONTHNAME function returns the full name of the month for a date.

Syntax:

MONTHNAME( date_value )

Parameters or Arguments:

date_value: A date or datetime value from which to extract the full month name.

Note: The MONTHNAME function returns the full name of the month (January, February, ... December) given a date value.

Example 1: Let's look at some MySQL MONTHNAME function examples and explore how to use the MONTHNAME function in MySQL.

mysql> SELECT MONTHNAME('2014-01-27');

Result:

mysql> SELECT MONTHNAME('2014-05-08');

Result:

mysql> SELECT MONTHNAME('2014-12-29');

Result:

Example 2: This remaining MONTHNAME instance could show the month call for the modern-day device date (modern-day device date is back via way of means of the CURDATE characteristic).

mysql> SELECT MONTHNAME(CURDATE()); 

APPROACH 3: Using FORMAT Function:

Use FORMAT characteristic that is added in Sql Server 2012 to get Month call from Date in Sql Server. It isn't an Sql Server local characteristic as an alternative it is .NET CLR based characteristic. The FORMAT() characteristic returns a price formatted withinside the particular layout and non-obligatory culture. You can use it to go back the month call from a date.

Example 1:

SELECT GETDATE() 'Today', FORMAT(GETDATE(),'MMMM') 'Month Name'

RESULT:

Example 2: The FORMAT() function returns a value formatted in the specified format and optional culture. You can use it to return the month name from a date.

DECLARE @date datetime2 = '2018-07-01';
SELECT FORMAT(@date, 'MMMM') AS Result;

Result:

In this case, I provided a custom date and time format string for returning the name of the month, the MMMM format string.

Example 3: The good thing about the FORMAT () function is that it takes an optional argument that allows you to specify the culture to use.

DECLARE @date datetime2 = '2018-07-01';
SELECT 
    FORMAT(@date, 'MMMM', 'en-US') AS 'en-US',
    FORMAT(@date, 'MMMM', 'es-es') AS 'es-es',
    FORMAT(@date, 'MMMM', 'de-de') AS 'de-de',
    FORMAT(@date, 'MMMM', 'zh-cn') AS 'zh-cn';

Result:

Example 4: If you do not provide a culture argument, the current session locale will be used. If you are not sure what language the current session is using, here are 3 ways to get the current session language. If you find yourself needing to change the language, here's how to set it to another language.

Here's an example that returns both the abbreviated and the full month name:

DECLARE @date datetime2(0) = '2028-09-01';

SELECT 
    FORMAT(@date, 'MMM', 'en-US') 'en-US',
    FORMAT(@date, 'MMM', 'es-es') 'es-es',
    FORMAT(@date, 'MMM', 'it') 'it'

UNION ALL

SELECT 
    FORMAT(@date, 'MMMM', 'en-US'),
    FORMAT(@date, 'MMMM', 'es-es'),
    FORMAT(@date, 'MMMM', 'it');

Result:

This is simply one in all many approaches to get the abbreviated month call from a date (even though this technique is arguably the best). There are at the least 4 extra approaches you could go back the abbreviated month call from a date.

Use the date_format function to format dates into month values ​​and group by month.

Syntax:

This is the syntax of the date_format function.

format_date(value, format string) 

In the above syntax, you must specify a literal value, a function, or other column name, and a format string specifying the format to which this value will be converted.

Example 1: Here is an example to convert a date string into month.

mysql> SELECT DATE_FORMAT("2020-06-15", "%M");

Example 2: Let us say you have a table sales(id, order_date, amount):

mysql> create table sales(id int, order_date date, amount int);

mysql> insert into sales(id,order_date,amount)
     values(1,'2020-10-01',150),
     (2,'2020-10-10',100),
     (3,'2020-11-05',250),
     (4,'2020-11-15',150),
     (5,'2020-12-01',350),
     (6,'2020-12-21',250);

mysql> select * from sales;

Output:

Example 3: Group By Month Name:

Here is the SQL query to group by month name.

mysql> select date_format(order_date, '%M'),sum(amount)
from sales
group by date_format(order_date, '%M');

Output:

Example 4: Group By Month and Year

Here is the SQL query to group by month and year.

mysql> select date_format(order_date, '%M %Y'),sum(amount)
from sales
group by year(order_date),month(order_date);

Output:

In the above query, we use date_format to convert date column’s values into month and year names. We also use YEAR() and MONTH() functions to ensure that data is grouped and ordered by month and year numbers.

Example 5: if we use date_format function in group by clause, then MySQL will sort the groups alphabetically, instead of chronologically. See below.

mysql> select date_format(order_date, '%M'),sum(amount)
from sales
group by date_format(order_date, '%M');

Output:

In the above SQL query we use date_format(order_date, “%M”) to convert a date column into month name, and use sum column to add up the sales amounts.

Sorts the rows by month number and specified month name (January should be displayed first and December should be displayed last).

Example 1: The birthday table contains two columns, name and birthday_month. Months are indicated by name, not numbers.

Let's create table,

If You want to sort the rows by birthday_month.

SELECT *
FROM birthday
ORDER BY STR_TO_DATE(CONCAT('0001 ', birthday_month, ' 01'), '%Y %M %d');

Output:

Discussion:

• To order by month, create a date with that month. To do this, use the STR_TO_DATE() function. If you have a date stored as a string in the format ``Year Month Day'', you can turn it into a date using STR_TO_DATE(date_string, '%Y%M%d').

First you need to create a string using the CONCAT() function. The year can be any year (eg 0001) and the date can be any date (eg 01):

CONCAT ('0001', birthday_month, '01') 

The CONCAT() function combines all all arguments into a single string. The string must be in the format "Year Month Day", so the second argument should be birthday_month; the first and third arguments just need to be in the correct format.

Then you need to convert this string to a date using the STR_TO_DATE(date_string, '%Y%M%d') function. The second argument of this function is the date format. %Y represents the year, %M represents the month (its full name, not the number), and %d represents the day.

STR_TO_DATE(CONCAT('0001', birthday_month, '01'), '%Y %M %d') 

Use it with the ORDER BY clause to sort rows in ascending order by date.

Example 2: If you'd like to see the rows in descending order, just append a DESC keyword, like this:

SELECT *
FROM birthday
ORDER BY STR_TO_DATE(CONCAT('0001 ', birthday_month, ' 01'), '%Y %M %d') DESC;

Note that in MySQL, NULLs are displayed first when sorting in ascending order and last when sorting in descending order. Also, the rows with the same birthday_month are displayed in random order (you may see Angie Julia third and Narelle Dillan fourth, or Narelle Dillan third and Angie Julia fourth).

Example 3: I actually have visible this question "How to apply Order through MONTH call of information kind CHAR/VARCHAR in SQL" in one of the forum. The motive why the programmer required this selection is he/she is storing Data, month and 12 months in three exclusive fields withinside the database. Even aleven though I don`t conform to save the dates cut up in 3 exclusive fields I notion I will discover how we will get favored end result kind through month that even a varchar/Char datatype field.

If you're an SQL developer this isn't a massive task. Let's test this with through developing a table.

CREATE TABLE [dbo].[Emp](

[ID] [nchar](10) NULL,
[Name] [nchar](10) NULL,
[Month] [nchar](10) NULL
) ON [PRIMARY]
GO

Now we will insert few records in the table.

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (1,'Asheej','March')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (2,'Priyabrata','May')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (3,'Santosh','December')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (4,'Yathish','November')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (5,'Mayur','January')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (6,'Ryan','June')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (7,'Karolina','February')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (8,'Shyam','April')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (9,'Sumit','October')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (10,'Prathibha','August')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (11,'Sowmya','July')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (12,'Ashish','September')

GO

First we will see what will be the result if we run the simple select query order by Month.

SELECT *  FROM [dbo].[Emp] order by [MONTH]

Output:

You can see the MONTH field order by alphebetically. So here comes the issue we want the data order by Month starting Jan, feb ..in the actual month order.

So to get that result we will write below query,

SELECT *  FROM [dbo].[Emp]

order by DATEPART(mm,CAST([MONTH]+ ' 1900' AS DATETIME)) asc

Output:

As usual you are always welcome to post your comment below.