January 22, 2009 at 12:58 pm
Hi
I am trying to write a query to get data between today and the date of the fourth friday of last month. Does any one know how to get the date of the fourth friday of last month.
very much appreciated.
January 22, 2009 at 1:29 pm
There are a number of ways to get that. The best one is build a calendar table and use that.
Beyond that, assuming you're using SQL 2005, here's one way:
;with
Numbers (Number) as
(select top 31 row_number() over (order by object_id)
from sys.all_objects),
LastMonth (MonthDay) as
(select dateadd(day, number,
dateadd(day, -1 * datepart(day, dateadd(month, -1, getdate())),
dateadd(month, -1, getdate())))
from Numbers),
Fridays (Friday, Seq) as
(select MonthDay, row_number() over (order by MonthDay)
from LastMonth
where datepart(weekday, MonthDay) = 6)
select Friday
from Fridays
where seq = 4
Of course, if you have a Numbers/Tally table, you can eliminate the first CTE and use that (table usually performs better than the CTE).
Also, if your server's first-day-of-the-week settings are different, you'll need to modify the Fridays CTE.
But what I really recommend is build a calendar table for this kind of thing.
- 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
January 22, 2009 at 2:36 pm
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply