August 11, 2008 at 12:19 pm
Hey all,
I know how to get the last day of the month, but how do I find each month end date for specified date range? So say I picked January ’08 to March ’08. I want to see the last day of the month for Jan, Feb and March.
Thanks
August 11, 2008 at 12:47 pm
You can use this procedure to get last day.
The catch in this is you have to pass 1st day in start and end date.
else need to modify proc to calculate the date.
DROP PROCEDURE GET_LAST_DAY_OF_MONTH
GO
CREATE PROCEDURE GET_LAST_DAY_OF_MONTH (@START_DATE DATETIME, @END_DATE DATETIME)
AS
-- NOTE : Always pass the 1st day of Month so that we can get last day
CREATE TABLE #LAST_DATES
(
LAST_DATE DATETIME
)
IF @END_DATE IS NULL
SET @END_DATE = GETDATE()
IF @START_DATE > @END_DATE
BEGIN
PRINT '--START DATE CAN NOT BE GREATER THAN END DATE---'
RETURN
END
WHILE @START_DATE <= @END_DATE
BEGIN
INSERT INTO #LAST_DATES
SELECT DATEADD(mm,1,@START_DATE) -1
SET @START_DATE = DATEADD(mm,1,@START_DATE)
END
SELECT LAST_DATE,DATENAME(dw,LAST_DATE) FROM #LAST_DATES
DROP TABLE #LAST_DATES
EXEC GET_LAST_DAY_OF_MONTH '01/01/2007', '01/01/2008'
August 11, 2008 at 2:12 pm
declare @SDate datetime, @EDate datetime
select @SDate = '1/8/2008', @EDate = '3/8/2008'
select
dateadd(month, number, -- Months in range
dateadd(day, -1 * datepart(day, @SDate), -- Last Day of (prior) month
dateadd(month, 1, -- Following month
dateadd(day, datediff(day, 0, @SDate), 0)))) -- Zero out time portion of inputs
as MonthEnd
from dbo.numbers -- table with numbers in it
where number <= datediff(month, @SDate, @EDate) -- Range set
It works from last calculation to first.
First it zeroes out any time portion of the input. You can ignore that if you either want the time portion or if you can guarantee that the inputs will never have times on them.
Next it finds the following month. This is the easiest way to get the last date.
Then it subtracts the day of the month. This gives the last day (of the prior month). Since we had it figure out the following month first, this gives us the last day of the @SDate month. (Add a month, then subtract the days.)
Then it uses a Numbers table to do the same thing for all months between the first one and the last one.
If you don't have a Numbers table (or something with the same data and a different name, like Tally or Integers), you can find scripts for creating one on this page, or just ask here and I'll give you the one that I use.
This should be plenty fast. I ran it on a century worth of months, and it took less than 1 millisecond to return the list of month-endings. It also does take leap years into account.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 11, 2008 at 2:12 pm
The Best way is to use a calendar table
then simply:
select month, max(day_of_month)
from calendar_table
where date_col >= 'date a' and date_col <='date b'
Cheers
* Noel
August 11, 2008 at 2:32 pm
Using a numbers table, you can do the following:
Declare @fromDate datetime
,@toDate datetime;
Set @fromDate = '20080115'; -- From date some date in January
Set @toDate = '20080315'; -- To date some date in March
Select dateadd(month, datediff(month, -1, @fromDate) + (n.Number - 1), -1)
From dbo.TableOfNumbers n
Where n.Number <= datediff(month, @fromDate, @toDate) + 1;
In the where clause, we need to add one to our datediff. The datediff will return 2 because there are only two month boundaries crossed between our two dates. Since we want all three month ends - we need to add one.
Replace the TableOfNumbers with whatever table you have in your system setup as a numbers table or Tally table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 11, 2008 at 2:39 pm
GSquared (8/11/2008)
declare @SDate datetime, @EDate datetime
select @SDate = '1/8/2008', @EDate = '3/8/2008'
select
dateadd(month, number, -- Months in range
dateadd(day, -1 * datepart(day, @SDate), -- Last Day of (prior) month
dateadd(month, 1, -- Following month
dateadd(day, datediff(day, 0, @SDate), 0)))) -- Zero out time portion of inputs
as MonthEnd
from dbo.numbers -- table with numbers in it
where number <= datediff(month, @SDate, @EDate) -- Range set
It works from last calculation to first.
First it zeroes out any time portion of the input. You can ignore that if you either want the time portion or if you can guarantee that the inputs will never have times on them.
Next it finds the following month. This is the easiest way to get the last date.
Then it subtracts the day of the month. This gives the last day (of the prior month). Since we had it figure out the following month first, this gives us the last day of the @SDate month. (Add a month, then subtract the days.)
Then it uses a Numbers table to do the same thing for all months between the first one and the last one.
If you don't have a Numbers table (or something with the same data and a different name, like Tally or Integers), you can find scripts for creating one on this page, or just ask here and I'll give you the one that I use.
This should be plenty fast. I ran it on a century worth of months, and it took less than 1 millisecond to return the list of month-endings. It also does take leap years into account.
Gus, I think this solution has one small problem. Given the dates input are in January and March, this code only returns the month end for February and March, but does not return the month end for January.
Two small corrections are needed here:
declare @SDate datetime, @EDate datetime
select @SDate = '1/01/2008', @EDate = '3/31/2008'
select
dateadd(month, number - 1, -- Months in range
dateadd(day, -1 * datepart(day, @SDate), -- Last Day of (prior) month
dateadd(month, 1, -- Following month
dateadd(day, datediff(day, 0, @SDate), 0)))) -- Zero out time portion of inputs
as MonthEnd
from Works.dbo.TableOfNumbers -- table with numbers in it
where number <= datediff(month, @SDate, @EDate) + 1-- Range set
Adding 1 to the where clause includes all three months. Subtracting 1 from number includes the January date.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 11, 2008 at 3:28 pm
I just ran my unmodified code on my machine, and got Jan 31, Feb 29 and Mar 31.
The difference is probably where your Numbers table starts. Mine starts at 0 (and goes through to 10-thousand). I bet yours starts at 1.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 11, 2008 at 8:01 pm
GSquared (8/11/2008)
I just ran my unmodified code on my machine, and got Jan 31, Feb 29 and Mar 31.The difference is probably where your Numbers table starts. Mine starts at 0 (and goes through to 10-thousand). I bet yours starts at 1.
Dang it - I knew that, but for whatever reason I didn't even check my table of numbers to verify where it started. I didn't create that table myself so just assumed - and we all know what happens when you assume 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 12, 2008 at 9:18 am
Jeffrey Williams (8/11/2008)
GSquared (8/11/2008)
I just ran my unmodified code on my machine, and got Jan 31, Feb 29 and Mar 31.The difference is probably where your Numbers table starts. Mine starts at 0 (and goes through to 10-thousand). I bet yours starts at 1.
Dang it - I knew that, but for whatever reason I didn't even check my table of numbers to verify where it started. I didn't create that table myself so just assumed - and we all know what happens when you assume 😉
Date functions like this are why I start mine at 0. Means I have to adjust my Where clauses in some cases, or I end up with things like substring(@String, 0, 1), which is empty, unless I make sure to omit the 0 in those cases. You have to judge which use will be more common and then remember to account for it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 12, 2008 at 9:52 am
August 12, 2008 at 11:03 am
Wow, didn't realize I got so much response ... I am not getting emailed of replies now, the hell?
Anyways, awesome, thanks guys. I've heard about the concept of a numbers table and such, but never used one. I am working on a huge rewrite to this report which is requiring this information, but we've changed it to the first day of each month vs. the last, so this should be MUCH easier.
I'm assuming the same concept applies ... use a date/numbers table? Basically I want to provide a drop down parameter of being able to select a start date range, to provide the user with all possible month start dates of the available data.
Where can I pick up this table?
Thanks
August 12, 2008 at 11:15 am
I ended up using this: http://www.angrycoder.com/article.aspx?ArticleID=334
Yet now that I only need the first date of the month, I don't think I need to take this route. I have a feeling it would be a performance hit comparing the date range available in my source table, with the actual dates of this based on the day.
That didn't come out right ... let me play for a bit.
August 12, 2008 at 11:19 am
Jeff Moden wrote a very good article on this table
http://www.sqlservercentral.com/articles/TSQL/62867/
He has a number of articles that have proven very helpful to me.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply