FAQ: return last day of the month


	About Us
	SSWUG Articles
	Articles
	FAQ

	Administration
	Backup/Restore
	Connectivity
	Development
	General
	Installation
	OLAP
	Replication
	Transfer/move
	Trouble
	SQL 6.5

	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

How do I return the last day of the month for the given date?

Answer:

To return the last day of the month for the given date, you can use the LastMonthDay user-defined function.

Syntax

LastMonthDay (date)

Arguments

date
Is the datetime value.

Return Types

datetime

The function's text:

CREATE FUNCTION dbo.LastMonthDay
  ( @Date datetime )
RETURNS datetime
AS
BEGIN
RETURN (CASE WHEN MONTH(@Date)= 12
THEN DATEADD(day, -1, CAST('01/01/' + STR(YEAR(@Date)+1) AS DateTime))
ELSE DATEADD(day, -1, CAST(STR(MONTH(@Date)+1) + '/01/' + STR(YEAR(@Date)) AS DateTime))
END)
END
GO

Example

Returns the last day for the '06/15/99' date:

SELECT dbo.LastMonthDay('06/15/99')
GO

Here is the result set:

------------------------------------------------------ 
1999-06-30 00:00:00.000

(1 row(s) affected)

Note. The user-defined functions are only available with SQL Server 2000, SQL Server 7.0 does not support user-defined functions.

See this article for more datetime user-defined functions:
Date and Time User-Defined Functions

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc