Problem:
You’d like to get the month from a date field in a SQL Server database.
Example:
Our database has a table named Furniture with data in the columns Id, Name, and ProducedDate.
| Id | Name | ProducedDate |
|---|---|---|
| 1 | sofa | 2018-01-10 |
| 2 | chair | 2018-01-05 |
| 3 | desk | 2018-06-20 |
| 4 | bookcase | 2018-11-15 |
Let’s get the month from each product’s ProducedDate and find out which furniture was produced in a given month.
Solution:
We’ll use the MONTH() function. Here’s the query you would write:
SELECT Name,MONTH(ProducedDate) AS ProducedMonthFROM Furniture;
Here’s the result of the query:
| Name | ProducedMonth |
|---|---|
| sofa | 1 |
| chair | 1 |
| desk | 6 |
| bookcase | 11 |
Discussion:
To get a month from a date field in SQL Server, use the MONTH() function. This function takes only one argument – the date. This can be a date or date and time data type. (In our example, the column ProducedDate is of the date data type.) The argument can be a column name or an expression. (In our example, it is the ProducedDate column).
MONTH() returns the month number as an integer from 1 to 12. For the desk item, the ProducedMonth value is 6 (returned from the 'YYYY-MM-DD' date '2018-06-20').