find the last month last friday date

Question

Post reply

find the last month last friday date

purushotham.k9

SSCommitted

Points: 1992
More actions
December 7, 2010 at 4:00 am

#146399

Can some please help me how to find the last month last friday date. Thanks mcuh

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

The main issue is finding last fridays data - you could use datepart(dw - but that will depend on the settings.

Safer is to count back

Also do you mean the previous friday 1 month ago or 1 month before the friday before today. I'll assume that you want a friday so the formet.

daclare @d datetime

select @d = dateadd(mm,-1,getdate()-1)

while datename(dw,@d) <> 'Friday'

select @d = @d - 1

if you want a single statement

;with cte as

(

select d = dateadd(mm,-1,getdate()-1), seq = 1

union all

select d = cte.d-1 from cte where seq < 7

)

select d

from cte

where datename(dw,d) = 'Friday'

Cursors never.
DTS - only when needed and never to control.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Have you considered a calendar table ?

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Clear Sky SQL
My Blog[/url]

purushotham.k9 SSCommitted Points: 1992 More actions · Answer 3

I have used the below sql to get the previous month last friday. How ever when am trying to check for next month its giving 24th dec as last friday but it should be 31st dec.

Please correct me the SQL where i missed

select CONVERT(char(10),dateadd(day, -7+(6-datepart(weekday,dateadd(month, datediff(month, -1, getdate())-1, 0)))%7,

dateadd(month, datediff(month, -1, getdate())-1, 0)),101)