Find Prior Week Ending Date 1

[ynnepztem]

Can anyone help me determine the date of the Friday prior to any given day. Ex: Today is Thursday, November 4, 2004.
I need a query that will tell me that last Friday was October 29, 2004.

Thanks

 

[dbomrrsm]

SET DATEFIRST 1
SELECT "Date" = 
case WHEN DATEPART(dw, GETDATE())= 1 THEN GETDATE()-3
 WHEN DATEPART(dw, GETDATE())= 2 THEN GETDATE()-4
 WHEN DATEPART(dw, GETDATE())= 3 THEN GETDATE()-5
 WHEN DATEPART(dw, GETDATE())= 4 THEN GETDATE()-6
 WHEN DATEPART(dw, GETDATE())= 5 THEN GETDATE()-7
 WHEN DATEPART(dw, GETDATE())= 6 THEN GETDATE()-1
 WHEN DATEPART(dw, GETDATE())= 7 THEN GETDATE()-2
END

[blue]DBomrrsm[/blue]

 

[donutman]

Without a case statement:

[Blue]DECLARE[/Blue] @TheDate [Blue]AS[/Blue] [Blue]datetime[/Blue]
[Blue]SET[/Blue] @TheDate[Gray]=[/Gray][red]'2004-11-05'[/red]
[Blue]SELECT[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Gray]-[/Gray][Fuchsia]DatePart[/Fuchsia][Gray]([/Gray]weekday[Gray],[/Gray]@TheDate[Gray])[/Gray][Gray]%[/Gray]7[Gray]-[/Gray]1[Gray],[/Gray] @TheDate[Gray])[/Gray]

If @TheDate is already a Friday then it uses the previous Friday. If you want Friday to stay Friday, then

[Blue]DECLARE[/Blue] @TheDate [Blue]AS[/Blue] [Blue]datetime[/Blue]
[Blue]SET[/Blue] @TheDate[Gray]=[/Gray][red]'2004-11-04'[/red]
[Blue]SELECT[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Gray]-[/Gray][Gray]([/Gray]1[Gray]+[/Gray][Fuchsia]DatePart[/Fuchsia][Gray]([/Gray]weekday[Gray],[/Gray]@TheDate[Gray])[/Gray][Gray])[/Gray][Gray]%[/Gray]7[Gray],[/Gray] @TheDate[Gray])[/Gray]

And you don't have to change the DateFirst.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

i have the answer

but first, a quick question: what if the date is a friday, do you want that same date or the date 7 days prior?

rudy

http://r937.com/

SQL Consulting

 

[donutman]

Give us both versions Rudy.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

same as yours, karl

rudy

http://r937.com/

SQL Consulting

 

[donutman]

DBomrrsm, has a good point. SET DateFirst should be used because not everyone installs SQL Server with the default DateFirst 7. And by doing so you don't have to use modulo arithmetic.

Solution keeping current Friday:

[Blue]SET[/Blue] [Black]DateFirst[/Black] 5
[Blue]DECLARE[/Blue] @TheDate [Blue]AS[/Blue] [Blue]datetime[/Blue]
[Blue]SET[/Blue] @TheDate[Gray]=[/Gray][red]'2004-11-05'[/red]
[Blue]SELECT[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Gray]-[/Gray][Fuchsia]DatePart[/Fuchsia][Gray]([/Gray]dw[Gray],[/Gray]@TheDate[Gray])[/Gray][Gray]+[/Gray]1[Gray],[/Gray] @TheDate[Gray])[/Gray]

Solution using previous Friday:

[Blue]SET[/Blue] [Black]DateFirst[/Black] 6
[Blue]DECLARE[/Blue] @TheDate [Blue]AS[/Blue] [Blue]datetime[/Blue]
[Blue]SET[/Blue] @TheDate[Gray]=[/Gray][red]'2004-11-05'[/red]
[Blue]SELECT[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Gray]-[/Gray][Fuchsia]DatePart[/Fuchsia][Gray]([/Gray]dw[Gray],[/Gray]@TheDate[Gray])[/Gray][Gray],[/Gray] @TheDate[Gray])[/Gray]

Trying to express what the formula does is pretty hard, but here's my attempt at it.
Think of the DatePart function as returning the number of days that have transpired since the DAY PRIOR to the DateFirst day of week. If you subtract that many days from the given date, you are returned to the DAY PRIOR to the previous DateFirst day of week. So by setting DateFirst 5, you will be returned to the DAY PRIOR to the previous Friday when you subtract the DatePart value from the given date. Since in this case that would be a Thursday you have to add 1 to get to Friday. Ouch!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]