Last Friday

Is there an easy way to find out what date was last friday to a given date? For example today is 20 Sep 2007, what would be the expression to find last friday to this date (14 Sep 2007) ?

Having a calendar table, always comes handy.

select *

from dbo.calendar

where dw = 'Friday' and dt = (select max(c2.dt) from dbo.calendar as c2 where c2.dt <= getdate())

Why should I consider using an auxiliary calendar table?

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

Another way, is using a known date that was Friday. This method was suggested by Steve Kass.

Code Snippet

declare @.d1 datetime, @.d2 datetime

set @.d1 = '19800104' -- Friday

set @.d2 = dateadd(day, datediff(day, 0, getdate()), 0) -- Today

select

dateadd(day, (datediff(day, @.d1, @.d2) / 7) * 7, @.d1)

AMB

|||

returns Invalid Object Name dbo.Calender

... I guess I need to create one, right.

|||Thanks AMB, the second solution worked, but I feel like a moron not being able to understand the logic. Would you enlighten me with the hidden wisdom please why the 2 sevens there in the last statement?|||

Very cool solution! I am going to have to throw that in my saved query folder.

Not to step on AMB's toes, but the explanation of the two sevens is:

1. The first datediff finds the number of days between the known friday and today.

2. Divide by seven to get an integer of number of weeks (rounded down) between the dates. The real division would return a decimal number, the decimal representing the days since last friday and the whole number being the weeks. This integer division will drop the day (decimal) portion of the number.

3. Multiply by 7 to get back to days. Since the division dropped the decimals, we get the weeks converted to days to the previous Friday.

4. Add that day number to the original Friday date and you will get the date from last friday.

|||

Yes, you have to create the calendar table. The link posted, can guide you on how to do it. for the second one, this seems to be the logic.

- From Friday to next Friday we have 7 days.

- how many 7 days, units of 7 days, do we have between the two dates

datediff(day, @.d1, @.d2) / 7

take in mind that the division between integers will yield an integer, no rest, any number of days between the las Friday and current date will not be taken in mind if current date is not a Friday also. So, if you multiply that number by 7 then you will have how many days between the initial date and last Friday.

Example:

Sep 14 - Friday 1

Sep 15 - 2

Sep 16 - 3

Sep 17 - 4

Sep 18 - 5

Sep 19 - 6

Sep 20 - 7

Sep 21 - 8

Sep 22 - 9

If we take Sep 14, 2007 as the known Friday and current date is 2007-09-22, then the difference in days is 8. That number divided by 7 is 1 (1 group of 7 days), multiply by 7 is 7.

8 / 7 = 1 (Saturday is not taken in mind and the same will happen if current date is between Sep 22 and Sep 27)

1 * 7 = 7

Sep 14, 2007 + 7 days = Sep 21, 2007

Let us say that current date is Sep 30.

datediff(day, @.d1, @.d2) = 16

16 / 7 = 2

2 * 7 = 14

dateadd(day, 14, @.d1) = Sep 28

In other words, the formula:

(datediff(day, @.d1, @.d2) / 7) * 7

is trimming whatever number of days from the last Friday, if current date is not a Friday too.

I do not know if it is more difficult to understand the formula than follow my English :-).

AMB

|||

Hi ShawnNWF,

I consider your explanation better than mine. Thanks for jumping in.

AMB

|||Thanks guys, both Shaun and AMB for the explanation.|||

Code Snippet

select dateadd(wk,datediff(wk,'January 5, 1900',getdate())-1,'January 5, 1900')