To pull last friday date

sql-server, t-sql

Solution

You can check to see if the current day is Friday with `DATENAME` and if so, just subtract 7 days, otherwise, use the function you made

SELECT CASE WHEN DATENAME(WEEKDAY, GETDATE()) = 'Friday' THEN
        CONVERT(DATE, DATEADD(DAY, -7, GETDATE()))
    ELSE
        DATEADD(d, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE())
    END AS 'LastFriday'

Problem

I have this query to pull the last friday date with given a date. It works for all day except if the given date is today. select dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate()) Taking for example, today is 2014-02-14. if i apply to the query above, the result returned is still 2014-02-14 dateadd(d, -((datepart(weekday, '2014-02-14') + 1 + @@DATEFIRST) % 7), '2014-02-14') If i put as 2014-02-13 below, then it returned last friday date properly. select dateadd(d, -((datepart(weekday, '2014-02-13') + 1 + @@DATEFIRST) % 7), '2014-02-13') Can you help how i can i get last Friday date if given today is Friday.

Original source