Months can be notoriously difficult to work with due to a variety of factors, including their variability in length. To make database developers' jobs easier, most relational databases (DBMS) offer functions such as MONTH() and MONTHNAME(). These two functions are great for grouping results by month and for displaying their values. In today's blog, we'll learn how to use specialized SQL functions for working with months.
Working with Month Functions
The MONTH() and MONTHNAME() functions are both implemented in MySQL. However, every database type has their own date and time functions, so you will likely have to refer to the documentation to find the equivalent functions for your database. To illustrate, SQL Server does not provide the MONTHNAME() function. Instead, DATENAME() may be employed to return any date part, include the month name.
Navicat greatly simplifies looking up the right date function for your DBMS via its auto-complete list. More than just built-in functions, it includes everything from tables and views to stored procedures and user functions. Here is the MONTH() function:
Once you select a function or procedure, it is inserted into the SQL Editor at the cursor position with tabbable input parameters, ready to be filled in:
MONTH() and MONTHNAME() Described
The MONTH() function accepts a date and returns an integer value which represents the month of a specified date between 1 and 12:
Meanwhile, MONTHNAME() returns the month name for a specified date:
Working with the MONTH() and MONTHNAME() Functions
Using both these functions together allows us to group and/or sort by month order while displaying the month name. To see them in action, let's write a query against the Sakila Sample Database that shows movie rentals for a given month. In case you aren't familiar with the Sakila Sample Database, it's a learning database for MySQL that represents a fictitious movie rental store chain. Here are the first several rows of the rental table in Navicat for MySQL:
Now, let's select the rental date and some film details, ordered by rental_date and film title:
To limit results to a given month, we can add a WHERE clause that includes the MONTH() function as follows:
To see the month name, we just have to include the MONTHNAME() function in the column list:
Filtering Results by Month AND Year
Without specifying a year in the WHERE clause, filtering results by a specified month will show results that span across ALL years of data. In many cases, this is not what you want. The solution is to include the YEAR() function, along with the MONTH() in the WHERE clause. Here's the updated query to limit results to May of 2005:
Conclusion
Database functions such as MONTH() and MONTHNAME() are highly useful for grouping results by month and for displaying their values. The two functions that we looked at today (three if you include YEAR()!) are supported by MySQL. For other databases, be sure to refer to the documentation to find their equivalent functions. After that, you can use Navicat's auto-suggest feature to insert the appropriate function into your SQL statements.
