Back to list SQL Server How to Get the Last Day of the Month in T-SQL Database: SQL Server Operators: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. 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: SQL Basics in SQL Server Common Functions in SQL Server Recommended articles: 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Get the Current Date (Without Time) in T-SQL How to Get the Current Date and Time (Without Time Zone) in T-SQL How to Subtract 30 Days from a Date in T-SQL How to Get the Last Day of the Month in T-SQL Tags: SQL Server Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.