locked
Get week ending date based on work date and week ending day number RRS feed

  • Question

  • Given a datetime and an int from a settings table which indicates what day of the week the pay period ends on, what would be the best way to get the datetime value of the week end date following the given work date?  Example:

    @workDate = '2012-10-02'

    @weekEndDay = 1

    Desired result based on these two inputs would be Sunday, 10/7.

    Tuesday, October 2, 2012 7:09 PM

Answers

  • A calendar table is always the best solution for this kind of stuff...

    But failing that the following should work for you...

    DECLARE 
    	@WorkDate DATETIME = '20121002', 
    	@WeekEndDay TINYINT = 1
    	
    SELECT 
    	CASE 
    		WHEN DATEPART(dw,@WorkDate) = @WeekEndDay
    		THEN @WorkDate
    		ELSE DATEADD(dd, (7 + @WeekEndDay) - DATEPART(dw,@WorkDate), @WorkDate)
    	END AS LastDayOfTheWeek


    Jason Long


    • Edited by Jason A Long Tuesday, October 2, 2012 7:31 PM
    • Proposed as answer by Naomi N Tuesday, October 2, 2012 7:40 PM
    • Marked as answer by ps0118mj Wednesday, October 3, 2012 4:07 PM
    Tuesday, October 2, 2012 7:30 PM
  • Another approach using set-based logic, quite easy to understand and almost impossible to forget:

    DECLARE @WorkDate DATETIME = '20121002', 
    	    @WeekEndDay int = 1;
    
    select @WorkDate+n.d
    from (values(1),(2),(3),(4),(5),(6),(7)) n(d)
    where @WeekEndDay = DatePart(dw,@WorkDate+n.d);


    • Proposed as answer by Naomi N Wednesday, October 3, 2012 3:10 PM
    • Marked as answer by ps0118mj Wednesday, October 3, 2012 4:07 PM
    Wednesday, October 3, 2012 10:03 AM

All replies

  • A calendar table is always the best solution for this kind of stuff...

    But failing that the following should work for you...

    DECLARE 
    	@WorkDate DATETIME = '20121002', 
    	@WeekEndDay TINYINT = 1
    	
    SELECT 
    	CASE 
    		WHEN DATEPART(dw,@WorkDate) = @WeekEndDay
    		THEN @WorkDate
    		ELSE DATEADD(dd, (7 + @WeekEndDay) - DATEPART(dw,@WorkDate), @WorkDate)
    	END AS LastDayOfTheWeek


    Jason Long


    • Edited by Jason A Long Tuesday, October 2, 2012 7:31 PM
    • Proposed as answer by Naomi N Tuesday, October 2, 2012 7:40 PM
    • Marked as answer by ps0118mj Wednesday, October 3, 2012 4:07 PM
    Tuesday, October 2, 2012 7:30 PM
  • Another approach using set-based logic, quite easy to understand and almost impossible to forget:

    DECLARE @WorkDate DATETIME = '20121002', 
    	    @WeekEndDay int = 1;
    
    select @WorkDate+n.d
    from (values(1),(2),(3),(4),(5),(6),(7)) n(d)
    where @WeekEndDay = DatePart(dw,@WorkDate+n.d);


    • Proposed as answer by Naomi N Wednesday, October 3, 2012 3:10 PM
    • Marked as answer by ps0118mj Wednesday, October 3, 2012 4:07 PM
    Wednesday, October 3, 2012 10:03 AM