Calculate "LastFriday" date

[imfarhan]

Hi
The following function calculate the last "Monday" value in MS Access (SQL) but I would like to calculate the "last Friday" of the week and could some one go through how this fucntion work (break down) please. I need to get the understanding so in future I can do my self instead of copy/paste
Many thanks

#Format(DateAdd("d", -(Weekday(Now()) - 2 +7), Now()), "dd/mm/yyyy"),#

If I break the above function to get more understaning
Weekday(Now()) = 2
Don't understand -->= so the answer is =
(Weekday(Now())-2+7) = 2-2+7 ? = 7 and further
-(Weekday(Now())-2+7) = 2-2+7 ? = 7 = -7
DateAdd("d", -7, ??) = ?? what value will come on that?

Don't understand , please could some one help me on that.

Many thanks

Regards
Farhan

 

[imfarhan]

The following query pulls the "Last Friday", even if the current day is firday as will which I wanted in my query.

SELECT <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
#7/9/2010# AS TestDate, <o></o>
Format(DateAdd("d",IIf(Weekday([TestDate])<7,-(Weekday([TestDate])+1),6-Weekday([TestDate])),[TestDate]),"dd/mm/yyyy") AS LastFriday<o></o>
FROM data;<o></o>
<o> </o>
<o>If you don't want to see Last Friday if the Testday=Friday change 7 to 6</o>
<o> </o>
<o>Regards,</o>
<o>Farhan</o>

 

[F&s anaylst]

Hi imfarhan,

if you are working with SQL bypass query you will be able to use this statement, this will return last friday date (change the 4 to different number to return different day). ensure you declare the variable before the select cause

declare @DATE datetime
set @DATE = DATEADD(wk, DATEDIFF(wk,0,GETDATE()-7), 4)
where [your time field] = @DATE