Get Last Friday's Date Unless Today Is Friday Using T-Sql

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

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 

Get the most recent Friday's date SQL

This works for any input and any setting of DATEFIRST:

dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate())

It works by adjusting the weekday value so that 0 = Friday, simulating Friday as the beginning of the week. Then subtract the weekday value if non-zero to get the most recent Friday.

Edit: Updated to work for any setting of DATEFIRST.

To pull last friday date

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'

SQL Server : function to display previous Friday's date if @date is not a Friday

The script you found works fine. You just need to write it into a function.

CREATE FUNCTION fnDateCheck
(
    @date datetime
)
RETURNS DATETIME
AS
    BEGIN 
        RETURN (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)
    END
GO

Retrieve the next friday, unless if it's already friday

It is easier to just subtract one day:

next_day(hire_date - interval '1' day, 'FRIDAY')

If I had to guess, the problem with your code is with spaces at the end of the day name.

Return Friday if day is weekend (Saturday-Sunday) in SQL Server

( @@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.

You can use the following code to push weekend dates back to the prior Friday:

-- Get the current date (without time).
declare @Today as Date = GetDate();
-- Move Saturday or Sunday dates back to the prior Friday.
select @Today as Today,
  case ( @@DateFirst + DatePart( weekday, @Today ) - 1 ) % 7 + 1
    when 1 then DateAdd( day, -2, @Today ) -- Sunday.
    when 7 then DateAdd( day, -1, @Today ) -- Saturday.
    else @Today end as ReportDate;

Previous Monday & previous Sunday's date based on today's date

Easy:

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)

EDIT:

The below will handle the Sunday date issue.

DECLARE @input varchar(10)
--SET @input = '9/9/2012' -- simulates a Sunday
SET @input = GETDATE()

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 6)

Need to pass a select to a variable to pick up last friday of 6 weeks ago

Your calculation for last Friday is correct, but unnecessarily complicated.

SELECT @endweek = dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate());
SELECT @startweek = DATEADD(WEEK, -6, @endweek)

EXEC YourProcedure
    @StartWeek = @startweek,
    @EndWeek = @endweek;

Is that what you were looking for?

---

Related Topics