EOMONTH() in SQL
Returns the last day of the month for a specified date with an optional month offset.
Syntax
Return type
EOMONTH() Function Example
What is EOMONTH() in SQL?
The EOMONTH() function in SQL returns the last day of the month for a given date, with an optional month offset. It is commonly used for financial calculations, reporting, and determining month-end deadlines.
This function is primarily supported in SQL Server. For other databases:
MySQL & PostgreSQL: Use
LAST_DAY(date_column).Oracle: Uses
LAST_DAY(date_column).SQLite: Uses
strftime('%Y-%m-%d', date_column, 'start of month', '+1 month', '-1 day').
Parameters:
date_expression: Date, datetime, or timestamp value
month_offset (optional): Number of months to offset (-12 to 12)
Example Use Cases:
-- Get end of current month
-- Get end of next month
-- Find last day transactions
Notes:
Behavior: Returns NULL if input is NULL
Performance Considerations: May impact index usage
Version Info: Not available in all DBMS versions
Deprecated/Recommended Alternatives: Some DBMS use LAST_DAY()
Error Handling:
Error: Returns NULL for invalid dates
Recommendation: Validate date format before using function
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
LAST_DAY()
LAST_DAY(date)
Returns NULL
PostgreSQL
DATE_TRUNC('MONTH', date) + INTERVAL '1 MONTH - 1 day'
DATE_TRUNC('MONTH', '2024-12-15') + INTERVAL '1 MONTH - 1 day'
Returns NULL
SQL Server
EOMONTH()
EOMONTH(date, [offset])
Returns NULL
SQLite
date(date, 'start of month', '+1 month', '-1 day')
date('2024-12-15', 'start of month', '+1 month', '-1 day')
Returns NULL
BigQuery
LAST_DAY()
LAST_DAY(date)
Returns NULL
Snowflake
LAST_DAY() or EOMONTH()LAST_DAY()
LAST_DAY(date)
Returns NULL
Athena
LAST_DAY()
LAST_DAY(date)
Returns NULL
