Get Previous Friday in SQL

Question

How to get previous friday in SQL Server?

Here is the code I have so far:

select (7 -datePart(dw, getdate()+3)) +1

Give us a couple example dates and what you want the output to be. — dfundako, Jun 15 '18 at 16:08
Possible duplicate of [Previous Monday & Previous Sundays date based on todays date](https://stackoverflow.com/questions/12422248/previous-monday-previous-sundays-date-based-on-todays-date) — JohnFx, Jun 15 '18 at 16:19
Please see my answer below. This should work for any date you input into the @date parameter. — Daniel Marcus, Jun 15 '18 at 16:21

score 2 · Answer 1 · answered Jun 15 '18 at 16:20

2

Use mod like this:

declare @date datetime = getdate()
declare @dow int = datepart(dw,@date)
declare @mod int = @dow % 7 +1
select cast(dateadd(d,-@mod ,@date) as date)

answered Jun 15 '18 at 16:20

Daniel Marcus

2,686
1
7
13

1

Tip: `( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1` will always return an integer from `1` to `7` with `1` corresponding to Sunday regardless of the setting of `DateFirst` or `Language`. – HABO Jun 16 '18 at 03:02

score 0 · Answer 2 · answered Jun 15 '18 at 18:49

If your week starts on Sunday, then here's a one-liner:

SELECT DATEADD(WEEK
              ,DATEDIFF(WEEK, '1900-01-05', DATEADD(DAY, 6 - DATEPART(DAY, GETDATE()) - 1, GETDATE()))
              ,'1900-01-05') AS [Last Friday]

The query counts the number of weeks since 1900-01-05 (which is a Friday) to today's date minus 1 week. This number of weeks is added to 1900-01-05 to get the previous week's Friday.

score 0 · Answer 3 · answered Jun 15 '18 at 19:40

0

Try this select convert(varchar(10), DATEADD(DD, -1 - DATEPART(DW, CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) ),CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))),101) It will give you previous friday of every week

answered Jun 15 '18 at 19:40

maddy

50
1
1
10

Get Previous Friday in SQL

3 Answers3