Get last Friday's Date unless today is Friday using T-SQL

Question

I'm trying to get the correct SQL code to obtain last Friday's date. A few days ago, I thought I had my code correct. But just noticed that it's getting last week's Friday date, not the last Friday. The day I'm writing this question is Saturday, 8/11/2012 @ 12:23am. In SQL Server, this code is returning Friday, 8/3/2012. However, I want this to return Friday, 8/10/2012 instead. How can I fix this code? Since we're getting to specifics here, if the current day is Friday, then I want to return today's date. So if it were yesterday (8/10/2012) and I ran this code yesterday, then I would want this code to return 8/10/2012, not 8/3/2012.

SELECT DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))

Solution 1

try this:

declare @date datetime;
set @date='2012-08-09'
SELECT case when datepart(weekday, @date) >5 then
 DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) 
else DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) end

result:

2012-08-03 

Example2:

declare @date datetime;
set @date='2012-08-10'
SELECT case when datepart(weekday, @date) >5 then
 DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) 
else DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) end

result:

  2012-08-10 

Solution 2

Modular arithmetic is the most direct approach, and order of operations decides how Fridays are treated:

DECLARE @test_date DATETIME = '2012-09-28'

SELECT DATEADD(d,-1-(DATEPART(dw,@test_date) % 7),@test_date) AS Last_Friday
      ,DATEADD(d,-(DATEPART(dw,@test_date+1) % 7),@test_date) AS This_Friday

Solution 3

Use this :

SELECT DATEADD(day, (DATEDIFF (day, '19800104', CURRENT_TIMESTAMP) / 7) * 7, '19800104') as Last_Friday

Solution 4

None of that? Try this:

DECLARE @D DATE = GETDATE()
SELECT DATEADD(D,-(DATEPART(W,@D)+1)%7,@D)

Solution 5

A tested function which works no matter what @@DATEFIRST is set to.

-- ==============
-- fn_Get_Week_Ending_forDate
-- Author:      Shawn C. Teague
-- Create date: 2017
-- Modified date:  
-- Description:   Returns the Week Ending Date on DayOfWeek for a given stop date
-- Parameters: DayOfWeek varchar(10) i.e. Monday,Tues,Wed,Friday,Sat,Su,1-7
--             DateInWeek DATE
-- ==============
CREATE FUNCTION [dbo].[fn_Get_Week_Ending_forDate] (
                   @DayOfWeek VARCHAR(10),@DateInWeek DATE)
RETURNS DATE
AS
BEGIN
   DECLARE @End_Date DATE
          ,@DoW TINYINT
   SET @DoW = CASE WHEN ISNUMERIC(@DayOfWeek) = 1 
                     THEN CAST(@DayOfWeek AS TINYINT)
                  WHEN @DayOfWeek like 'Su%' THEN 1
                  WHEN @DayOfWeek like 'M%'  THEN 2
                     WHEN @DayOfWeek like 'Tu%' THEN 3
                  WHEN @DayOfWeek like 'W%'  THEN 4
                  WHEN @DayOfWeek like 'Th%' THEN 5
                  WHEN @DayOfWeek like 'F%'  THEN 6
                  ELSE  7
               END

   select @End_Date = 
          CAST(DATEADD(DAY,
               CASE WHEN (@DoW - (((@@datefirst) + datepart(weekday, @DateInWeek)) % 7)) = 7
                        THEN 0 
                    WHEN (@DoW - (((@@datefirst) + datepart(weekday, @DateInWeek)) % 7)) < 0 
                        THEN 7 - ABS(@DoW - (((@@datefirst) + datepart(weekday, @DateInWeek)) % 7))
                    ELSE (@DoW - (((@@datefirst) + datepart(weekday, @DateInWeek)) % 7) ) 
               END
               ,@DateInWeek) AS DATE)

   RETURN @End_Date
END

View more solutions

Comments

[MacGyver]

it does not. it gives 8/3/2012, not 8/10/2012, just like mine

[MacGyver]

I'll up-vote you and accept a week from now if all goes well. So far, so good. I will test each day of the week. The top one meets my requirements. Thanks.

[Krishna Thota]

I'm sorry my friend. I thought Last week's Friday. I corrected the query. I hope this helps.

[Joe G Joseph]

@MacGyver: I have updated my query, Please check it now..and for testing you can change the date and see..

[MacGyver]

If run Mon, Tue, Wed or Thu, this code still won't work correctly. "SELECT DATEADD(day, +4 - (DATEPART(dw, GETDATE()) + @@DATEFIRST-2) % 7, GETDATE()) AS LastFriday". Replace GETDATE() with a variable and you'll see. I think to solve the logic within my question, we need a case or if statement like @joe has.

[Krishna Thota]

I think you didn't get a thing. The date shows based on the server from which the sql server is running on. if you change the date on the server from which the sql server database is running, this will do the trick. Try to change your system date and try the query on the local database. If you want last monday, Tuesday. try changing the "+4" value on the query.

[t-clausen.dk]

This will work different on different database settings, you should always try to avoid using datefirst

[Muflix]

seems to me that This_Friday does not work, because today is 25.2.2016 that's mean Friday is 26, but variable shows 19.2.2016 which is same as Last_Friday

[Zeina]

how do i customize this code on Wednesday, or any other day?

[Mike]

@Zeina to get other days of the week you have to know that the last DATEADD is subtracting from the Monday of whatever week you starting from. So the -3 from Monday = Friday. If you want to have the previous Wednesday then you would need to change it to -5 so it would be DATEADD(DAY, -5, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0))because 5 days before Monday is Wednesday.

[Kevin Swann]

Be careful with this solution as it is dependent on the current value of DATEFIRST. The solution only works if the DATEFIRST is set to 7, the usual default.