Getting the week number of a passed in Date

Question

Post reply

Getting the week number of a passed in Date

Trystan Hughes

SSCrazy

Points: 2109
More actions
February 28, 2006 at 7:45 am

#365353

Hi all,
I have a SP in work that returns the week number of a date that is passed into the SP as a parameter. But I think the SP is a bit of a mouth full, and can be shrinked down quite a bit but am not sure how to go about it, as I don't really know what is needed, and what is not. The SP is as follows:
CREATE PROCEDURE u_GetWeekNo
@ARG_CheckDate Datetime,
@ARG_WeekNo Integer Output
AS
Declare @CheckDate Datetime, @StartOfPrevYear Datetime
Declare @AdjustWeek Int, @OldAdjustWeek Int, @PrevYearAdjust Int, @SavedYear Int
Declare @AdjustedEndYear Varchar(3)
SET DATEFIRST 1
Set NOCOUNT ON
Set RowCount 0
Select @CheckDate = '01 Jan ' + Convert(Char(4),Datepart(yyyy,@ARG_CheckDate))
Select @SavedYear = 0
While CONVERT(datetime,CONVERT(varchar,@CheckDate,106)) <= CONVERT(datetime,CONVERT(varchar,@ARG_CheckDate,106))
Begin
-- Check Adjustments at the beginning of the year
If @SavedYear <> Datepart(yyyy,@CheckDate)
Begin
Select @AdjustedEndYear = 'No'
Select @AdjustWeek = 0
If datepart(d,@CheckDate) >= 1
Begin
If datepart(dw,@CheckDate) > 4
Begin
-- Check Previous Year Adjustments
select @StartOfPrevYear = dateadd(yyyy,-1,'01 Jan ' + convert(char(4),datepart(yyyy,@CheckDate)))
If Datepart(wk,@StartOfPrevYear) = 1 and datepart(dw,@StartOfPrevYear) > 4
Select @PrevYearAdjust = -1
Else
Select @PrevYearAdjust = 0
Select @AdjustWeek = datepart(wk,(dateadd(d,-4,@CheckDate))) - 1 + @PrevYearAdjust
Select @AdjustedEndYear = 'Yes'
End
Else
Begin
If @AdjustedEndYear = 'Yes' or (datepart(dw,@CheckDate) = 1 and datepart(d,@CheckDate) > 1)
Select @AdjustWeek = -1
Else
Select @AdjustWeek = 0
Select @SavedYear = Datepart(yyyy,@CheckDate)
End
End
End
-- Check Adjustments at the end of the year
Select @OldAdjustWeek = @AdjustWeek
If Datepart(wk,@CheckDate) + @AdjustWeek = 53
If datepart(dw,'31 Dec ' + convert(char(4),datepart(yyyy,@CheckDate))) < 4
Select @AdjustWeek = -52
If @CheckDate = @ARG_CheckDate
Begin
Select @ARG_WeekNo = Datepart(wk,@CheckDate) + @AdjustWeek
Return 0
End
Select @CheckDate = dateadd(d,1,@CheckDate)
Select @AdjustWeek = @OldAdjustWeek
End
Can anyone identify what can be removed without effecting the working functionality of the SP?
Thanks
Tryst

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

CREATE PROCEDURE u_GetWeekNo

@ARG_CheckDate Datetime,

@ARG_WeekNo Integer Output

AS

select @arg = datepart( wk, @arg_checkdate)

return

JeffB Hall of Fame Points: 3541 More actions · Answer 2

You may want to try using a function for something like this, depending on the usage of the statement.

create function fnGetWeekNo

(

@CheckDate Datetime

)

returns integer

as

begin

return (select datepart( wk, @CheckDate))

end

go

select dbo.fnGetWeekNo('01 Mar 2005')

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 3

So will this return the week number for the week in that current year (i.e Week 9 for end of Feb) and not return the week number from the date base date in which SQL Server uses, which I think is 1900?

Thanks

Tryst

JeffB Hall of Fame Points: 3541 More actions · Answer 4

Yes, it returns the week of the year. Try this:

select datepart( wk, '28 Feb 2006')

Ian Yates SSCoach Points: 19738 More actions · Answer 5

And you'll want to ensure that you have SQL's first day of the week variable appropriately set