Get the DateTime corresponding to the last read or Fri or Mon, etc. In SQL Server 2012

I have a specific DATETIME value and I would like to get the DATETIME value for a given day of the week 'n' (where n is an integer from 1 to 7) that is immediately before the given date.

Question . How would I do this given the currentDate value and the lastWeekDay value?

For example, if the given date is 06/15/2015 in the format mm / dd / yyyy, then what is the date of the day of week 6 that was submitted before 06/15/2012. In this example, the specified date is Monday and we want the date last Friday (i.e. Weekday = 6).

declare @currentDate datetime, @lastWeekDay int;
set @currentDate = getdate();
set @lastWeekDay = 6;--this could be any value from 1 thru to 7
select @currentDate as CurrentDate, '' as LastWeekDayDate --i need to get this date

      

UPDATE 1

In addition to Anon's excellent answer, I also found an alternative way to do this as shown below.

DECLARE @currentWeekDay INT;
SET @currentWeekDay = DATEPART(WEEKDAY, @currentDate);

--Case 1: when current date week day > lastWeekDay then subtract 
--        the difference between the two weekdays
--Case 2: when current date week day <= lastWeekDay then go back 7 days from
--        current date, and then add (lastWeekDay - currentWeekDay)

SELECT
  @currentDate AS CurrentDate,
  CASE
    WHEN @currentWeekDay > @lastWeekDay THEN DATEADD(DAY, -1 * ABS(CAST(@lastWeekDay AS INT) - CAST(@currentWeekDay AS INT)), @currentDate)
    ELSE DATEADD(DAY, @lastWeekDay - DATEPART(WEEKDAY, DATEADD(DAY, -7, @currentDate)), DATEADD(DAY, -7, @currentDate))
  END AS LastWeekDayDate;

      

+3


source to share


1 answer


Calculate how many days have passed since a fixed date, mod 7, and subtract from the input date. The magic number "5" is associated with the fact that Date Zero (1900-01-01) is Monday. Going 5 days makes the @lastWeekDay range [1..7] comparable to the weekday range [Sunday..Saturday].

SELECT DATEADD(day,-DATEDIFF(day,5+@lastWeekDay,@currentDate)%7,@currentDate)

      



I am avoiding the function DATEPART(weekday,[...])

because ofSET DATEFIRST

+3


source







All Articles