Free Tools

Resources

Blog

Docs

Pricing

Book a Demo

Get Free Extension

Get Sherloq Free

Back to Library

MONTH() in SQL

Extracts the month value (1-12) from a date or timestamp expression.

Syntax

MONTH(date_expression) 
-- Some databases use: EXTRACT(MONTH FROM date_expression)

Return type

INTEGER (1-12)

MONTH() Function Example

SELECT MONTH(order_date) FROM orders; 
-- Output: 12 (month extracted from date)

What is MONTH() in SQL?

The MONTH() function in SQL extracts the month value (1-12) from a given date or timestamp expression. It is commonly used for date-based filtering, grouping data by month, and financial or seasonal analysis.

This function is supported in multiple databases:

MySQL, PostgreSQL, and SQL Server: Use MONTH(date_column).
Oracle: Uses EXTRACT(MONTH FROM date_column).
SQLite: Uses strftime('%m', date_column).

Parameters:

date_expression: Date, datetime, or timestamp value

Example Use Cases:

-- Monthly sales analysis

SELECT MONTH(sale_date) AS month, SUM(revenue) FROM sales GROUP BY MONTH(sale_date); 
-- Output:
month    sum_revenue
-----    -----------
1        128450.75
2        145678.90
3        167234.56
4        189567.23
5        198345.67
6        210456.78
7        199876.54
8        187654.32
9        178923.45
10       165432.12
11       156789.34
12       145678.90

-- Find records for specific month

SELECT * FROM transactions WHERE MONTH(transaction_date) = 6; 
-- Output:
transaction_id    transaction_date    amount    status
--------------    ----------------    ------    -------
T125             2024-06-01          299.99    Complete
T126             2024-06-05          567.89    Complete
T127             2024-06-12          789.45    Pending
T128             2024-06-15          234.56    Complete
T129             2024-06-28          445.67    Processing

-- Get season statistics

SELECT COUNT(*) AS winter_sales FROM orders WHERE MONTH(order_date) IN (12, 1, 2);
-- Output:
winter_sales
------------
456

Notes:

Behavior: Returns NULL if input is NULL
Performance Considerations: May affect index usage
Version Info: Core function available in most DBMS
Deprecated/Recommended Alternatives: Some DBMS prefer EXTRACT()

Error Handling:

Error: Returns NULL for invalid dates
Recommendation: Validate date format before extraction

Supported Databases:

DBMS

Function / Syntax

Example

Behavior with NULL

MySQL

MONTH()

MONTH(date)

Returns NULL

PostgreSQL

EXTRACT(MONTH FROM)

EXTRACT(MONTH FROM date)

Returns NULL

SQL Server

MONTH()

MONTH(date)

Returns NULL

SQLite

strftime('%m')

strftime('%m', date)

Returns NULL

BigQuery

EXTRACT(MONTH FROM)

EXTRACT(MONTH FROM date)

Returns NULL

Snowflake

MONTH()

MONTH(date)

Returns NULL

Athena

EXTRACT(MONTH FROM)

EXTRACT(MONTH FROM date)

Returns NULL

Related Functions:

YEAR()

Extracts the year value from a date or timestamp expression.

DAY()

Extracts the day of the month (1-31) from a date or timestamp expression.

DATEFROMPARTS()

Creates a date value from specified year, month, and day integers.

DATEFORMAT()

Converts a date/timestamp expression into a specified string format.

EOMONTH()

Returns the last day of the month for a specified date with an optional month offset.

MONTH()

Extracts the month value (1-12) from a date or timestamp expression.

DATEDIFF()

Calculates the difference between two dates in specified interval units.

DATEADD()

Adds or subtracts a specified time interval from a date value.

GETDATE()

Returns the current date and time of the database server.

One place for all your queries,
directly on your SQL editor

Get Free Extension

Book a Demo

Get Sherloq Free

MONTH() in SQL

Syntax

Return type

MONTH() Function Example

What is MONTH() in SQL?

Parameters:

Example Use Cases:

Notes:

Error Handling:

Supported Databases:

DBMS

Function / Syntax

Example

Behavior with NULL

MySQL

PostgreSQL

SQL Server

SQLite

BigQuery

Snowflake

Athena

Related Functions:

YEAR()

DAY()

DATEFROMPARTS()

DATEFORMAT()

EOMONTH()

MONTH()

DATEDIFF()

DATEADD()

GETDATE()

One place for all your queries, directly on your SQL editor

Explore

Resources

Free Tools

Legal

Explore

Resources

Free Tools

Legal

Explore

Resources

Free Tools

Legal

One place for all your queries,
directly on your SQL editor