Back to cookbooks list Articles Cookbook

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

  • EOMONTH()

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.

IdNamePurchaseDate
1sofa2019-02-10
2desk2019-04-01
3bookcase2019-05-20

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

Download SQL Server Cheat Sheet

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:

NamePurchaseDateLastDayDate
sofa2019-02-102019-02-28
desk2019-04-012019-04-30
bookcase2019-05-202019-05-31

Discussion:

If you would like to get the date containing the last day of the month of a given date, use the EOMONTH() function. 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() returns 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()’s 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:

NamePurchaseDateLastDayDate
sofa2019-02-102019-03-30
desk2019-04-012019-05-31
bookcase2019-05-202019-06-30

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:

NamePurchaseDateLastDayDate
sofa2019-02-102019-01-31
desk2019-04-012019-03-31
bookcase2019-05-202019-04-30

Recommended courses:

Recommended articles:

See also: