Rick Caminiti dotCOM

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION getWeekByDoW

(

      – Add the parameters for the function here

      @date datetime,

    @day    varchar(3)

)

RETURNS datetime

AS

BEGIN

      DECLARE @dowNum   int,

                  @truncDate datetime,

                  @retDate datetime;

 

      select @truncDate = cast(convert(varchar, @date, 101) as datetime)

           ,@dowNum =

                  case

                        when @day = ‘Sun’

                              then 1

                        when @day = ‘Mon’

                              then 2

                        when @day = ‘Tue’

                              then 3

                        when @day = ‘Wed’

                              then 4

                        when @day = ‘Thu’

                              then 5

                        when @day = ‘Fri’

                              then 6

                        when @day = ‘Sat’

                              then 7

                        else 0

                  end

 

      select @retDate = DATEADD(DD, @dowNum - DATEPART(DW, @truncDate), @truncDate)

      RETURN @retDate

END

GO

 

select      getWeekByDoW(getdate(), ‘Sun’) Sun,

            getWeekByDoW(getdate(), ‘Mon’) Mon,

            getWeekByDoW(getdate(), ‘Tue’) Tue,

            getWeekByDoW(getdate(), ‘Wed’) Wed,

            getWeekByDoW(getdate(), ‘Thu’) Thu,

            getWeekByDoW(getdate(), ‘Fri’) Fri,

            getWeekByDoW(getdate(), ‘Sat’) Sat

 

Sun	Mon	Tue	Wed	Thu	Fri	Sat
6/28/2009	6/29/2009	6/30/2009	7/1/2009	7/2/2009	7/3/2009	7/4/2009