Get last Friday's Date unless today is Friday

问题:

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

回答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 

回答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

回答3:

Use this :

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

回答4:

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

回答5:

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

回答6:

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

回答7:

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

回答8:

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

回答9:

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

回答10:

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]
      ------------------------------------------------------------------------------------------------------------------------------------------------
;

回答11:

None of that? Try this:

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