Last Sunday

• barkha.singh

  SSC-Addicted

  Points: 417

  More actions

  May 11, 2009 at 7:33 am

  #204772

  Comments posted to this topic are about the item Last Sunday

• Javed-330570

  SSC Veteran

  Points: 254

  More actions

  August 19, 2009 at 8:42 am

  #1041584

  The function below returns the week ending date for the specified week day.

  -- =============================================

  -- Author: Barkha Javed

  -- Create date: 10 Jul 2009

  -- Description: Date of week ending, per given weekday

  -- =============================================

  ALTER FUNCTION [dbo].[fn_LastWeek]

  ( @Duration int,

  @DayOfWeek varchar(10) = Null

  )

  RETURNS nchar(8)

  AS

  BEGIN

  DECLARE @dt nchar(8)

  DECLARE @DiffDays int

  SELECT @DiffDays = CASE WHEN @DayOfWeek Like 'Mon%' THEN '2'

  WHEN @DayOfWeek Like 'Tue%' THEN '3'

  WHEN @DayOfWeek Like 'Wed%' THEN '4'

  WHEN @DayOfWeek Like 'Thu%' THEN '5'

  WHEN @DayOfWeek Like 'Fri%' THEN '6'

  WHEN @DayOfWeek Like 'Sat%' THEN '7'

  WHEN @DayOfWeek Like 'Sun%' THEN '1'

  ELSE '1' END

  SELECT @dt = Convert(nchar(8),DateAdd(d, -(DatePart(w, getdate())-@DiffDays), DateADD(wk, @Duration, getdate())),1)

  RETURN @dt

  END