September 9, 2009 at 7:56 pm
I have a table that maintains events. Each event has a start and end date. I need to be able to see which months each event occurs in based on the start and end dates. For example:
If event Sales Blitz ABC starts on 1/1/2009 and ends on 4/30/2009 I would need to know, by query, that the event occurred in January, February, March, and April. How can I achieve this?
September 9, 2009 at 8:10 pm
It depends on how exactly you need the output. If you just need to see if a date is between the start and end date, it's a simple check. If you need an actual list of the months, you can use a Tally/Numbers table. (See the link in my signature for an explanation of what that is).
There are dozens of ways to get this information depending on the specifics. Can you be more specific?
September 9, 2009 at 8:31 pm
I need to be able to take a startdate and an enddate and show the list of dates or months between the two. For dates
StartDate: 1/1/09
EndDate: 1/7/09
Result:
1/1/09
1/2/09
1/3/09
1/4/09
1/5/09
1/6/09
1/7/09
Basically, the intent is to be able to see the dates on which an event will occur based on the start and end.
For months:
StartDate: 1/1/09
EndDate 4/30/09
Result:
January
February
March
April
September 9, 2009 at 9:03 pm
Yeah, Tally table is a viable option here.
Here's a couple hints to get you started.
SELECT DATEADD(mm,N,StartDate)
WHERE DATEADD(mm,N,StartDate) < EndDate
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply