How to Find the Last Day of the Month in SQL, MySQL, PostgreSQL, and Teradata

SQL End Of Month Lead

Working with DATE and DATETIME values in SQL-type languages can be tricky sometimes! There are 12 months, anywhere from 28 to 31 days in each month, and either 365 or 366 days in each year (for leap years)! This makes it hard to standardize months and date periods in your queries.

We often need to organize data by month, but with different month lengths, this can be hard! A standard practice is to assign all rows with a date in the month to a column labeled with the last day of that month. Or, maybe you need to figure out the date one or two months from now, but don’t know how long the month will be.

How do you find the last day of the month for a given date? The answer is different, depending on which type of SQL database you use. This tutorial will show you your options for T-SQL, MySQL, PostgreSQL, and Teradata…

The EOMONTH() Function (SQL Server 2012 and newer)

If you are using SQL Server 2012, 2014, or newer, you have it easy! There is an excellent built-in function called EOMONTH() that can automatically provide you with a DATETIME output date that is the end of the month for the date specified, or any number of months in the past or in the future.

EOMONTH() Syntax

The syntax for EOMONTH() is as follows:

EOMONTH(start_date, [months_ahead_or_behind])

start_date can take a DATE/DATETIME or a string (VARCHAR or NVARCHAR). It will implicitly convert what you give it to a DATE format, so don’t try to feed it non-date text, or you’ll get conversion errors.

[months_ahead_or_behind] takes an INT (signed integer) and adds or subtracts months from the date you provide in start_date. If you leave it blank, it will assume 0 (zero) and go to the end of the month in start_date.

EOMONTH() Examples and Output

Let’s see how EOMONTH() operates with some standard inputs.

DECLARE @datetime DATETIME
SET     @datetime = '2015-01-02 03:04:05'

SELECT
     @datetime              "DATETIME Value"
    ,EOMONTH(@datetime)     "EOMONTH Default"
    ,EOMONTH(@datetime, 0)  "EOMONTH Current Month"
    ,EOMONTH(@datetime, 1)  "EOMONTH Next Month"
    ,EOMONTH(@datetime,-1)  "EOMONTH Previous Month"

On SQL Server 2012 and newer, this query produces the following output.

DATETIME ValueEOMONTH DefaultEOMONTH Current MonthEOMONTH Next MonthEOMONTH Previous Month
January, 02 2015 03:04:052015-01-312015-01-312015-02-282014-12-31

You can experiment with this code and output on SQL Fiddle by clicking here.

Notice that the output of EOMONTH() is a DATE, not a DATETIME. It will not give you the last hour, minute, or second of the last day of the month. It will instead convert to midnight on the last day of the month. If you need more specific DATETIME results, look in the section for SQL Server 2008 and earlier.

The LAST_DAY() and DATE_ADD() Functions (MySQL, Oracle)

Similar to EOMONTH() on SQL Server 2012, MySQL and Oracle have a function called LAST_DAY() that is almost as powerful. It will always return the DATE value of the last day of the month of the date specified.

If you want to get the last day of a previous or future month, you need to combine this function with the DATE_ADD() function.

LAST_DAY() & DATE_ADD() Syntax

The syntax for LAST_DAY() is as follows:

LAST_DAY(start_date)

start_date can take a DATE/DATETIME or a string (VARCHAR or NVARCHAR). It will implicitly convert what you give it to a DATE format, so don’t try to feed it non-date text, or you’ll get conversion errors.

To get add or subtract months from the end of month date, you can use the DATE_ADD() function. The syntax for DATE_ADD() is as follows:

DATE_ADD(start_date, INTERVAL number interval_type)

start_date can take a DATE/DATETIME or a string (VARCHAR or NVARCHAR). It will implicitly convert what you give it to a DATE format, so don’t try to feed it non-date text, or you’ll get conversion errors.

interval_type can have many values, but to add or subtract months, you would want it to be MONTH. Other valid options include DAY, WEEK, YEAR, and HOUR.

number is the integer number of units you want to add or subtract from the start_date. To add months into the future from start_date, make number positive. To subtract months, make number negative.

LAST_DAY() & DATE_ADD() Examples and Output

Let’s see how LAST_DAY() and DATE_ADD() operate.

SET @datetime := '2015-01-02 03:04:05';

SELECT
     @datetime                                        "DATETIME Value"
    ,LAST_DAY(@datetime)                              "LAST_DAY Result"
    ,LAST_DAY(DATE_ADD(@datetime, INTERVAL 1 MONTH))  "DATE_ADD Next Month"
    ,LAST_DAY(DATE_ADD(@datetime, INTERVAL -1 MONTH)) "DATE_ADD Previous Month";

In MySQL, this query produces the following output.

DATETIME ValueLAST_DAY ResultDATE_ADD Next MonthDATE_ADD Previous Month
2015-01-02 03:04:05January, 31 2015 00:00:00February, 28 2015 00:00:00December, 31 2014 00:00:00

You can experiment with this code and output on SQL Fiddle by clicking here.

Wrapping the @datetime variable in a DATE_ADD() function can let you add or subtract months from the date.

Notice that the output of LAST_DAY() is a DATETIME set at midnight. It will not give you the last hour, minute, or second of the last day of the month.

The DATEADD() and DATEDIFF() Functions (SQL Server 2008 and older)

If you tried the EOMONTH() function above in SQL Server, you may have gotten the following error message:

'EOMONTH' is not a recognized built-in function name.

This is  a sign that the database you are accessing is using SQL Server 2008 or earlier (2005, etc.). This can happen even if you are running SQL Server Management Studio from 2012, 2014, or newer. If this happens to you, there are another set of functions that can achieve the same result. The code is a bit messier, but you’ll get what you need!

To get EOMONTH()-like end of month results in SQL Server 2008, you will need to combine DATEADD() and DATEDIFF().

DATEADD() & DATEDIFF() Syntax

The syntax for DATEADD() is as follows:

DATEADD(datepart, number, start_date)

datepart is the part of the date to want to modify. Acceptable inputs include MONTH, SECOND, YEAR, etc. Our examples will use MONTH and SECOND.

number is the integer number of units you want to add or subtract from the start_date. To add months/seconds/etc. into the future from start_date, make number positive. To subtract months/seconds/etc., make number negative.

start_date can take a DATE/DATETIME or a string (VARCHAR or NVARCHAR). It will implicitly convert what you give it to a DATE format, so don’t try to feed it non-date text, or you’ll get conversion errors.

The syntax for DATEDIFF() is as follows:

DATEDIFF(countpart, start_date, end_date)

countpart is the part of the date to want to measure. Acceptable inputs include MONTH, SECOND, YEAR, etc. Our examples will use MONTH.

start_date and end_date can take a DATE/DATETIME or a string (VARCHAR or NVARCHAR). It will implicitly convert what you give it to a DATE format, so don’t try to feed it non-date text, or you’ll get conversion errors.

DATEADD() & DATEDIFF() Examples and Output

Let’s see how DATEADD() and DATEDIFF() work together to calculate end of month values like EOMONTH().

DECLARE @datetime DATETIME
SET     @datetime = '2015-01-02 03:04:05'

SELECT
     @datetime  "DATETIME Value"
    ,DATEADD(MONTH, DATEDIFF(MONTH, 0, @datetime) + 1, 0) - 1 "DATEADD/DATEDIFF Midnight Result"
    ,DATEADD(SECOND, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @datetime) + 1, 0)) "DATEADD/DATEDIFF Second Result"

In SQL Server 2008, this query produces the following result:

DATETIME ValueDATEADD/DATEDIFF Midnight ResultDATEADD/DATEDIFF Second Result
2015-01-02 03:04:05.0002015-01-31 00:00:00.0002015-01-31 23:59:59.000

You can experiment with this code and output on SQL Fiddle by clicking here.

To understand how this trick works, it helps to go through it step-by-step…

  1. The DATEDIFF() function counts the number of months between the date “0” (January 1, 1900) and @datetime (January 2, 2015). -> 1380
  2. It adds one more to it. -> 1381
  3. This becomes the input to the DATEADD() function. The DATEADD() function adds that number of months (1381) to the date “0” (January 1, 1900). -> 02/01/2015
  4. Then we subtract one more day to get the end of the month. -> 01/31/2015

Note that depending on how you build your query, you can produce a result that displays the last day of the month at midnight, or the last second of the last day of the month – the true “end of the month”. The second result option follows a similar procedure but only takes a second off the end of the date, to arrive at the true end-of-the-month.

The ADD_MONTHS() and EXTRACT() Functions (Teradata)

Much like SQL Server 2008, Teradata does not have an EOMONTH()-like end of month function. Teradata does have an ADD_MONTHS() function that works a lot like DATEADD().  We can use that to get our last day of the month date stamp.

ADD_MONTHS() & EXTRACT() Syntax

The syntax for ADD_MONTHS() is as follows:

ADD_MONTHS(start_date, number_of_months)

start_date can take a DATE/DATETIME or a string (VARCHAR or NVARCHAR). It will implicitly convert what you give it to a DATE format, so don’t try to feed it non-date text, or you’ll get conversion errors.

number_of_months is the integer number of months you want to add or subtract from the start_date. To add months into the future from start_date, make number positive. To subtract months, make number negative.

In order to use the correct start_date for ADD_MONTHS(), we’ll need to find the day component of the date. For that, we use the EXTRACT() function.

The syntax for EXTRACT() is as follows:

EXTRACT(datepart FROM start_date)

datepart is the part of the date to want to measure. Acceptable inputs include DAY, MONTH, SECOND, YEAR, etc. Our examples will use DAY.

start_date can take a DATE/DATETIME or a string (VARCHAR or NVARCHAR). It will implicitly convert what you give it to a DATE format, so don’t try to feed it non-date text, or you’ll get conversion errors.

ADD_MONTHS() & EXTRACT() Examples and Output

Let’s see how ADD_MONTHS() and EXTRACT() work together to calculate end of month values like EOMONTH().

--Run if repeating query to reset volatile table.
--DROP TABLE v;

--Set temp variable SampleDate
CREATE VOLATILE TABLE v AS
(
SELECT DATE '2015-01-02' AS SampleDate
)
WITH DATA PRIMARY INDEX (SampleDate)
ON COMMIT PRESERVE ROWS;

SELECT
     v.SampleDate "DATE"
    ,ADD_MONTHS(v.SampleDate - EXTRACT(DAY FROM v.SampleDate) + 1, 1) - 1 "ADD_MONTHS Result"
    ,ADD_MONTHS(v.SampleDate - EXTRACT(DAY FROM v.SampleDate) + 1, 2) - 1 "Next Month Result"
    ,v.SampleDate - EXTRACT(DAY FROM v.SampleDate) "Prev. Month Result"
;

In Teradata, this produces the following results:

DATEADD_MONTHS ResultNext Month ResultPrev. Month Result
1/2/20151/31/20152/28/201512/31/2014

To use ADD_MONTHS() to get the end of month result, this query performs several operations:

  1. We start with the SampleDate. -> 01/02/2015
  2. Then we subtract the number of days in the SampleDate (2) from the sample date using EXTRACT(). -> 12/31/2015
  3. Then we add one more day back. -> 01/01/2015
  4. This becomes the start_date parameter for the ADD_MONTHS() function. The ADD_MONTHS() function adds one month to the date. -> 02/01/2015
  5. Then we subtract one day from the ADD_MONTHS() result. -> 1/31/2015

This process works the same to get the last day of the next month. We just change the number_of_months parameter to 2 instead of 1.

Notice that if we want the last day of the previous month, we don’t even need ADD_MONTHS()! We just subtract the number of days in the SampleDate from the SampleDate and stop right there (step 2 above).

The DATE_TRUNC() Function (PostgreSQL)

PostgreSQL also lacks a LAST_DAY() or EOMONTH() function, so we must create our own operation based on the Postgres DATE_TRUNC() function. We can use this to get our last day of the month date stamp.

DATE_TRUNC() Syntax

The syntax for DATE_TRUNC() is as follows:

DATE_TRUNC(datepart, start_date)

start_date can take a DATE/TIMESTAMP or a string (VARCHAR or NVARCHAR). It will implicitly convert what you give it to a TIMESTAMP format, so don’t try to feed it non-date text, or you’ll get conversion errors.

datepart is the part of the date to want to truncate. Acceptable inputs include 'DAY', 'MONTH', etc. Our examples will use 'MONTH'.

DATE_TRUNC() Examples and Output

Let’s see how DATE_TRUNC() works to calculate end of month values like EOMONTH().

-- PostgreSQL has no temp variables, hence the TIMESTAMP
SELECT
     TIMESTAMP '2015-01-02 03:04:05' "DATE"
    ,DATE_TRUNC('MONTH', TIMESTAMP '2015-01-02 03:04:05') + INTERVAL '1 MONTH - 1 DAY' "DATE_TRUNC Result"
    ,DATE_TRUNC('MONTH', TIMESTAMP '2015-01-02 03:04:05') + INTERVAL '2 MONTH - 1 DAY' "Next Month Result"
    ,DATE_TRUNC('MONTH', TIMESTAMP '2015-01-02 03:04:05') - INTERVAL '1 DAY' "Previous Month Result"
;

In PostgreSQL, this produces the following results:

DATEDATE_TRUNC ResultNext Month ResultPrevious Month Result
January, 02 2015 03:04:05January, 31 2015 00:00:00February, 28 2015 00:00:00December, 31 2014 00:00:00

You can experiment with this code and output on SQL Fiddle by clicking here.

To use DATE_TRUNC() to get the end of month result, this query performs several operations:

  1. We start with the timestamp. -> 01/02/2015 03:04:05
  2. Then the DATE_TRUNC() function “truncates” the timestamp information to the month level, keeping the year and month and discarding the day and time. -> 01/01/2015
  3. Then we add one month minus one day from the DATE_TRUNC() result. -> 1/31/2015

This process works the same to get the last day of the next month. We just change the INTERVAL parameter to ‘2 MONTH’ instead of ‘1 MONTH’.

Notice that if we want the last day of the previous month, the INTERVAL becomes ‘1 DAY’ and is subtracted rather than added.

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with data for over a decade. He also runs Excel Tactics, finding solutions for everyday issues in Microsoft Excel. SQL Tactics is dedicated to helping you master your databases one query at a time.

Leave a Reply

Your email address will not be published. Required fields are marked *