Jul 17, 2002 #1 poobalanm Programmer Jul 17, 2002 1 0 0 MY 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
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
Jul 17, 2002 #2 MissTipps Programmer May 20, 2002 91 0 0 GB 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 Upvote 0 Downvote
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
Jul 17, 2002 #3 Bernadette MIS Nov 16, 2000 203 0 0 GB 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 Upvote 0 Downvote
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
Jul 17, 2002 #4 MatthewPeters Technical User Aug 13, 2001 81 0 0 US 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 Upvote 0 Downvote
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