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))

Joe G Joseph · Accepted Answer · 2012-08-11T06:05:58.853

15

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

edited Aug 11 '12 at 06:05

answered Aug 11 '12 at 05:53

Joe G Joseph

23,518
5
56
58

2

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. – JustBeingHelpful Aug 11 '12 at 06:01
1

@MacGyver: I have updated my query, Please check it now..and for testing you can change the date and see.. – Joe G Joseph Aug 11 '12 at 06:11
how do i customize this code on Wednesday, or any other day? – Zeina Aug 11 '16 at 12:58
1

@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. – Mike Aug 15 '17 at 15:33

score 11 · Answer 2 · edited Sep 28 '12 at 11:41

11

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

edited Sep 28 '12 at 11:41

fancyPants

50,732
33
89
96

answered Sep 28 '12 at 11:33

Michael

111
2

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 – Muflix Feb 25 '16 at 10:26

score 5 · Answer 3 · answered Aug 11 '12 at 06:18

5

Use this :

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

answered Aug 11 '12 at 06:18

Habib Zare

1,206
8
17

score 3 · Answer 4 · answered May 30 '17 at 20:29

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

score 3 · Answer 5 · answered Jun 06 '18 at 16:14

3

None of that? Try this:

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

answered Jun 06 '18 at 16:14

Cory

71
2

Krishna Thota · Answer 6 · 2012-08-11T06:08:37.130

1

This will give you the Friday of Last week.

SELECT DATEADD(day, -3 - (DATEPART(dw, GETDATE()) + @@DATEFIRST - 2) % 7, GETDATE()) AS LastWeekFriday

This will give you last Friday's Date.

SELECT DATEADD(day, +4 - (DATEPART(dw, GETDATE()) + @@DATEFIRST-2) % 7, GETDATE()) AS LastFriday

edited Aug 11 '12 at 06:08

answered Aug 11 '12 at 05:43

Krishna Thota

6,646
14
54
79

it does not. it gives 8/3/2012, not 8/10/2012, just like mine – JustBeingHelpful Aug 11 '12 at 05:59
I'm sorry my friend. I thought Last week's Friday. I corrected the query. I hope this helps. – Krishna Thota Aug 11 '12 at 06:09
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. – JustBeingHelpful Aug 11 '12 at 14:06
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. – Krishna Thota Aug 12 '12 at 04:39
1

This will work different on different database settings, you should always try to avoid using datefirst – t-clausen.dk Aug 12 '12 at 07:45

score 1 · Answer 7 · edited Nov 08 '13 at 16:13

1

select convert(varchar(10),dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate()),101)

edited Nov 08 '13 at 16:13

Spudley

166,037
39
233
307

answered Nov 08 '13 at 15:55

Dishant Daryani

11
1

score 1 · Answer 8 · answered Mar 30 '15 at 16:47

Following code can be use to return any last day by replacing @dw_wk, test case below use friday as asked in original questions

DECLARE @date SMALLDATETIME
,@dw_wk INT --last day of week required - its integer representation
,@dw_day int --current day integer reprsentation
SELECT @date='8/11/2012'
SELECT @dw_day=DATEPART(dw,@date)
SELECT @dw_wk=DATEPART(dw,'1/2/2015') --Just trying not to hard code 5 for friday, here we can substitute with any date which is friday
SELECT case when @dw_day<@dw_wk then DATEADD(DAY, @dw_wk-7-@dw_day,@date) else DATEADD(DAY,@dw_wk-@dw_day, @date) END

Michael Plautz · Answer 9 · 2016-11-07T19:46:32.027

Here's an answer I found here adapted from MySQL to T-SQL that is a one liner using all basic arithmetic (no division or modulos):

SELECT DATEADD(d, 1 - datepart(weekday, dateadd(d, 2, GETDATE())), GETDATE())

You can do all sorts of combinations of this, like get next Friday's date unless today is Friday, or get last Thursday's date unless today is Thursday by just changing the 1 and the 2 literals in the command:

Get next Friday's date unless today is Friday

SELECT DATEADD(d, 7 - datepart(weekday, dateadd(d, 1, GETDATE())), GETDATE())

Get last Thursday's date unless today is Thursday

SELECT DATEADD(d, 1 - datepart(weekday, dateadd(d, 3, GETDATE())), GETDATE())

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. — Kevin Swann, Aug 21 '17 at 15:17

score 1 · Answer 10 · answered Jul 11 '17 at 23:13

I have had this same issue, and created the following example to show how to do this and to make it flexible to use whichever day of the week you want. I have different lines in the SELECT statement, just to show what this is doing, but you just need the [Results] line to get the answer. I also used variables for the current date and the target day of the week, to make it easier to see what needs to change.

Finally, there is an example of results when you want to include the current date as a possible example or when you always want to go back to the previous week.

DECLARE @GetDate AS DATETIME = GETDATE();
DECLARE @Target INT = 6 -- 6 = Friday

SELECT
        @GetDate                                                                                                                                AS [Current Date]       ,
        DATEPART(dw, @GetDate)                                                                                                                  AS [Current Day of Week],
        @Target                                                                                                                                 AS [Target Day of Week] ,
             IIF(@Target = DATEPART(dw, @GetDate), 'Yes'   , 'No')                                                                              AS [IsMatch]            ,
             IIF(@Target = DATEPART(dw, @GetDate), 0       ,               ((7 + @Target - DATEPART(dw, @GetDate)) % 7) - 7)                    AS [DateAdjust]         ,
      ------------------------------------------------------------------------------------------------------------------------------------------------
        CAST(IIF(@Target = DATEPART(dw, @GetDate), @GetDate, DATEADD(d, (((7 + @Target - DATEPART(dw, @GetDate)) % 7) - 7), @GetDate)) AS DATE) AS [Result]
      ------------------------------------------------------------------------------------------------------------------------------------------------
;
SELECT
        @GetDate                                                                               AS [Current Date]       ,
        DATEPART(dw, @GetDate)                                                                 AS [Current Day of Week],
        @Target                                                                                AS [Target Day of Week] ,
                           ((7 + @Target - DATEPART(dw, @GetDate)) % 7) - 7                    AS [DateAdjust]         ,
      ------------------------------------------------------------------------------------------------------------------------------------------------
        CAST(DATEADD(d, (((7 + @Target - DATEPART(dw, @GetDate)) % 7) - 7), @GetDate) AS DATE) AS [NOTIncludeCurrent]
      ------------------------------------------------------------------------------------------------------------------------------------------------
;

score 0 · Answer 11 · answered Jun 27 '17 at 09:42

0

SELECT DECODE(TO_CHAR(SYSDATE,'DY'),'FRI',SYSDATE,NEXT_DAY(SYSDATE, 'FRI')-7) FROM dual;

answered Jun 27 '17 at 09:42

Md Sultan

39
5

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

11 Answers11

Linked