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;
source to share
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
source to share