Finding Last Day of the Month IN SQL Server

Finding Last Day of the Month IN SQL Server

Certain applications require business logic to determine the last day of the month. This is the case for accounting applications that perform such tasks as invoice processing and customer billing. After seeing this logic implemented in various means, even as simple as harcoding the last day of each month, I developed a simple function that does the same:

 DECLARE @inDate   	AS DATETIMEDECLARE @tempDate 	AS DATETIMEDECLARE @lastDayOfMonth AS DATETIME/*incoming date to use to determine the last day of the month*/SET @inDate = '1/1/2000' /* take the date and add one month */SET @tempDate = DateAdd(MONTH, 1 , @inDate)/* create a date MM/01/YYYY and subtract 1 day so that    it returns the last day of the month*/SET @lastDayOfMonth = DateAdd(DAY, -1 , 			CONVERT(DATETIME, CONVERT(char(2), MONTH(@tempDate)) + 			'/01/' +  			CONVERT(char(4), YEAR(@tempDate))))PRINT 'Last Day of ' + DATENAME(month, @inDate) +       ' is ' + CONVERT(VARCHAR, @lastDayOfMonth)

Therefore the above T-SQL script uses January 1, 2000 and determine the last day of January is the 31st. The output is:

 Last Day of January is Jan 31 2000 12:00AM

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist