How to Get the Last Day of the Month in T-SQL

Problem:

You’d like to find the last day of the month for a specific date in T-SQL.

Example:

Our database has a table named Furniture with data in the columns Id, Name, and PurchaseDate.

Let’s get products’ names and purchase dates and the last day of the month when these items were purchased.

Solution:

We’ll use the function EOMONTH() to find the last day of the month.

SELECT 
  Name,
  PurchaseDate,
  EOMONTH(PurchaseDate) AS LastDayDate
FROM Furniture;

Here’s the result of the query:

Discussion:

If you would like to get the date containing the last day of the month of a given date, use the EOMONTH(). This function takes one mandatory argument: a date (or date and time), which can be a date/datetime column or an expression that returns a date. (In our example, we use the PurchaseDate column.)

EOMONTH() return the last day of the same month as the argument. The above query for the sofa with the purchase date '2019-02-10' returns '2019-02-28'; in February 2019, the last day of the month was the 28th.

If you want to return the last day of the second, third, etc. month from a given date, use EOMONTH() optional second argument: the number of months to add. Look at the example:

SELECT 
  Name,
  PurchaseDate,
  EOMONTH(PurchaseDate, 1) AS LastDayDate
FROM Furniture;

Here’s the result of the query:

For the sofa with the purchase date '2019-02-10', this query returns '2019-03-30' – the last day of the next month. We’ve added one month to the end of the current month.

If you would like to find the last day of the month before a given month, use a negative value (e.g. -1) as the second argument:

SELECT 
  Name,
  PurchaseDate,
  EOMONTH(PurchaseDate, -1) AS LastDayDate
FROM Furniture;

Here’s the result of the query: