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