Get week number with changed @@datefirst question

I want to get the week number (according to ISO rule i.e. if most of the working days fall in the JAN set it to as week number 1).

I know one stored procedure is available at MSDN to achieve this task. It works fine for me if the @.@.datefirst is set to 1 (i.e. Monday) but i have a strange requirement of calculating the week number according to ISO rule but the start date of week can be any day for example Saturday. When I try to run that procedure with my unique criteria I get wrong week number for some years

Can any one tell me the more generic solution?

Your help is appreciated

thanx

Maybe this helps:

SET DATEFIRST 6

Can you post the code of your stored Procedure, please?

|||

What I understood is,

1. Need to find the Week Number for the Given Date

2. If the Jan-01 of the year fall after Wednesday then it will not be considered as Week1 & it will be counted as Week-52 of previous Year

3. If the Jan-01 of the Year fall before or on Wed then it will be considered as Week1 of the current Year

4. The same adjustment will be taken place for every date..

If I understand correctly then the following query will help you

Create Function dbo.MyWeekNo(@.DateValue as DateTime) Returns Int
As
Begin
Declare @.Date as Datetime
declare @.Date2 as Datetime
Declare @.Week as int
Select @.Date = Convert(Varchar,Year(@.DateValue)) + '-01-01', @.Date2=DateAdd(DD,-1,@.Date)

Select @.Week = Case When WeekNo=0 Then dbo.MyWeekNo(@.Date2) Else WeekNo End
From
(
Select
Case When DatePart(W,@.Date) >= 3 Then DatePart(WW,@.DateValue) -1
Else DatePart(WW,@.DateValue) End WeekNo
) as Weeks

Return @.Week;
End

Go

select dbo.MyWeekNo('2003-01-05')

|||

ManiD wrote:

What I understood is,

1. Need to find the Week Number for the Given Date

2. If the Jan-01 of the year fall after Wednesday then it will not be considered as Week1 & it will be counted as Week-52 of previous Year

3. If the Jan-01 of the Year fall before or on Wed then it will be considered as Week1 of the current Year

4. The same adjustment will be taken place for every date..

If I understand correctly then the following query will help you

Create Function dbo.MyWeekNo(@.DateValue as DateTime) Returns Int
As
Begin
Declare @.Date as Datetime
declare @.Date2 as Datetime
Declare @.Week as int
Select @.Date = Convert(Varchar,Year(@.DateValue)) + '-01-01', @.Date2=DateAdd(DD,-1,@.Date)

Select @.Week = Case When WeekNo=0 Then dbo.MyWeekNo(@.Date2) Else WeekNo End
From
(
Select
Case When DatePart(W,@.Date) >= 3 Then DatePart(WW,@.DateValue) -1
Else DatePart(WW,@.DateValue) End WeekNo
) as Weeks

Return @.Week;
End

Go

select dbo.MyWeekNo('2003-01-05')

i have the same problem as ur code prevails i.e. if I try your code following values

set datefirst 6

select dbo.MyWeekNo('1983-12-31')

It give me week 53 but as Saturday (Day 6) is the sarting day of week it shoud be set to week 1

ne more solution

|||

Zadoras wrote:

set datefirst 6

select dbo.MyWeekNo('1983-12-31')

In this function we are not changing the DATEFIRST option.. We are using the default value 1. The function will find the Rite values for you...(The logic will take care this..)

Try the following query..

Set DateFirst 1

Select dbo.MyWeekNo('1983-12-31')

|||

Can you try this Procedure?

CREATE PROCEDURE CustomWeekNr
( @.DateToCheck SMALLDATETIME
, @.WeekStart INT
, @.WeekNr INT OUT
)
AS
BEGIN
DECLARE @.Offset INT;
DECLARE @.YearOfCheck VARCHAR(10);
DECLARE @.DayNr INT;
SET DATEFIRST @.WeekStart;
-- Get YEAR-01-01
SELECT @.YearOfCheck = CAST(DATEPART(YEAR, @.DateToCheck) AS VARCHAR(4)) + '-01-01';
-- Define Offset for First Week of the Year
SELECT @.Offset = CASE
WHEN DATEPART(WEEKDAY, @.YearOfCheck) > 4 THEN 8 - DATEPART(WEEKDAY, @.YearOfCheck)
ELSE 1 - DATEPART(WEEKDAY, @.YearOfCheck)
END
-- Day/Year from the given Date - Offset
SELECT @.DayNr = DATEPART(DAYOFYEAR, @.DateToCheck) - @.Offset
-- Calculate the WeekNr
SELECT @.WeekNr = CASE
WHEN @.DayNr % 7 = 0 THEN @.DayNr/7
ELSE @.DayNr/7+1
END
-- When Week Nr = 53: Possible? If not Set Week = 1
-- Only if (Offset -3) or (Offset < -1 And Days/Year = 366)
IF @.WeekNr = 53
BEGIN
IF @.Offset > -2 OR (DATEPART(DAYOFYEAR, CAST(DATEPART(YEAR, @.DateToCheck) AS VARCHAR(4)) + '-12-31') = 366 AND @.Offset > -3)
BEGIN
SET @.WeekNr = 1
END
END
END

-- Returning the Week-Nr for a Specific Date AND Startday of Week (In this case, Saturday (6)):
DECLARE @.MyWeek INT;
EXEC CustomWeekNr '1983-12-31', 6, @.MyWeek OUT
SELECT @.MyWeek

|||

I gave a try but found the following problem such as

3 January 1986
2 January 1987
1 January 1988 etc are set to 0 (should be 1 for first two and 52 for last one)

|||

Maybe it's better to use a CLR (C# or VB .NET Assembly), because all the logic is computed faster in these Languages. Code can be found here:

http://konsulent.sandelien.no/VB_help/Week/

The calculation is made for First Day of Week is Monday => the decisive Day is Thursday. So for your Problem you have to calculate it for Tuesday.

Or you can port the code to SQL....

|||

thannx for your support... I had a breif look to your link but I think it is not the generic code as it is hard coding the day name (i.e. thursday in your case) it is perfectly alright

but my scenario is different. chances are more that in our database they will be more than 1 starting day of the week it may be saturday , monday or even sunday.... so is it possible that i can define a single procedure to do that or I have to define for every datefirst value?

|||

You can put something like that to your procedure:

SELECT CASE
WHEN (@.@.datefirst + 4) < 8 THEN @.@.datefirst + 4
ELSE @.@.datefirst + 4 - 7
END

Then you have the flexibility you need.

|||

Assuming that all guesses haven't solved your problem (because no hint is marked as answer), here's the complete function (its independent from the setting of @.@.datefirst, because it's used for calculate the first day of the year).

I have tested it with your dates:

3 January 1986: Week 1
2 January 1987: Week 1
1 January 1988: Week 53

CREATE FUNCTION dbo.IsoWeek
(
@.SearchDate AS DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @.FirstDay DATETIME, @.SearchYear INT, @.WeekNo INT;
-- Get the Year of the Searched Date and the First Day of WEEK 1
SELECT @.SearchYear = YEAR(@.SearchDate);
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
-- Calculate WEEK for Easy Dates (Exclude 29/30/31 Dec and 01/02/03 Jan When its not equal to @.FirstDay)
IF @.SearchDate > CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-03' AS DATETIME) AND @.SearchDate < CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-12-29' AS DATETIME)
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
ELSE
BEGIN
-- Calculate WEEK for 01/02/03 Jan
IF @.SearchDate < CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME)
BEGIN
IF @.SearchDate >= @.FirstDay
SELECT @.WeekNo = 1;
ELSE
BEGIN
-- Calculate WEEK using the last Year
SELECT @.SearchYear = YEAR(@.SearchDate) - 1;
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
END
END
-- Calculate WEEK for 29/30/31 Dec
ELSE
BEGIN
-- If @.SearchDate >= @.StartDay of Next Year => WEEK 1
SELECT @.SearchYear = YEAR(@.SearchDate) + 1;
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
IF @.SearchDate >= @.FirstDay
SELECT @.WeekNo = 1;
ELSE
BEGIN
-- Normal Calculation
SELECT @.SearchYear = YEAR(@.SearchDate);
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
END
END

END
RETURN @.WeekNo;
END

|||

PLEASE GIVE ME SOME TIME TO CHECK THIS OUT

I HOPE IT WORKS

THANX VERY MUCH FOR YOUR SUPPORT

|||

Lucky P wrote:

Assuming that all guesses haven't solved your problem (because no hint is marked as answer), here's the complete function (its independent from the setting of @.@.datefirst, because it's used for calculate the first day of the year).

I have tested it with your dates:

3 January 1986: Week 1
2 January 1987: Week 1
1 January 1988: Week 53

CREATE FUNCTION dbo.IsoWeek
(
@.SearchDate AS DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @.FirstDay DATETIME, @.SearchYear INT, @.WeekNo INT;
-- Get the Year of the Searched Date and the First Day of WEEK 1
SELECT @.SearchYear = YEAR(@.SearchDate);
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
-- Calculate WEEK for Easy Dates (Exclude 29/30/31 Dec and 01/02/03 Jan When its not equal to @.FirstDay)
IF @.SearchDate > CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-03' AS DATETIME) AND @.SearchDate < CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-12-29' AS DATETIME)
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
ELSE
BEGIN
-- Calculate WEEK for 01/02/03 Jan
IF @.SearchDate < CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME)
BEGIN
IF @.SearchDate >= @.FirstDay
SELECT @.WeekNo = 1;
ELSE
BEGIN
-- Calculate WEEK using the last Year
SELECT @.SearchYear = YEAR(@.SearchDate) - 1;
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
END
END
-- Calculate WEEK for 29/30/31 Dec
ELSE
BEGIN
-- If @.SearchDate >= @.StartDay of Next Year => WEEK 1
SELECT @.SearchYear = YEAR(@.SearchDate) + 1;
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
IF @.SearchDate >= @.FirstDay
SELECT @.WeekNo = 1;
ELSE
BEGIN
-- Normal Calculation
SELECT @.SearchYear = YEAR(@.SearchDate);
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
END
END

END
RETURN @.WeekNo;
END

Thanx for your time Lucky

Still doesnt solve my problem

for example, for some years it is setting Friday week number as 0 for 1 January 1982 if the DateFirst is set to 6 (Saturday) and second problem, I noticed , for dates 28 December 1985, 29 December 1985, 30 December 1985, 31 December 1985 it is setting the value week 53 (if date first is 6, saturday) but it should be week 1 as most of the working days are in that week and next week is 1 which should be week 2

I have tried many things but nothing is working for me

i guess i have to desing different procedures for different datefirst

thanx for all those who contributed in this thread