Retrieve end of month date

[poobalanm]

Hi,

How can i retrieve the last date of each month using select statements and date/time functions? for example:

Month Last Date
January 2002 31-01-2002
February 2002 28-02-2002
March 2002 31-03-2002

and so on..

thank you

 

[MissTipps]

I don't know if there's a better way, but I would move to the first day of each month then use DATEADD(dd,-1, My_Date).

Its very simple and works well.

Cheers

Clare

 

[Bernadette]

Hi There

This will return the last day of the month for you. It uses GetDate() to get todays date to get the current month


Declare @mydate datetime
Set @mydate = '<insert your date here>'

SELECT DATEADD(day, -1, DATEADD(month, 1, DATEADD(day,
(1-DATEPART(day,@mydate)), @mydate)))


Hope This Helps Bernadette

 

[MatthewPeters]

Here is a SP that will return a recordset containing the last day of each month:

CREATE Procedure uspLastDateOfMonths

AS

DECLARE @iCount tinyint
DECLARE @iMonth tinyint
DECLARE @StartDate datetime
DECLARE @LastDate datetime

CREATE TABLE #EndDates(MonthID tinyint, LastDayOfMonth char(10))

set @iCount=1
WHILE (@iCount <=12)
BEGIN
set @iMonth = @iCount
set @StartDate= str(@iMonth)+'/28/'+ltrim(str(year(getdate())))

set @LastDate= DATEADD(day, -1, DATEADD(month, 1, DATEADD(day,
(1-DATEPART(day,@StartDate)), @StartDate)))

insert #EndDates (MonthID, LastDayOfMonth) values (@iMonth,
convert(char(10), @LastDate, 101) )
set @iCount = @iCount +1
END

Select MonthID, LastDayOfMonth from #EndDates
Drop Table #EndDates