February 5, 2010 at 12:03 am
Hi ,
I am thankfull to all forum members who helped me in all ways and made me learn many new tricks in sql 2005.
Now i have come across a tricky requirement by the client, where he wants week should start from 'Monday' and end at 'Sunday',
And also '4 Jan 2010' to '10 Jan 2010' should come under the fisrt week of 2010.
I found that by default Microsoft takes Sunday or 7 as first day of the week.
Now this can be changed with below command,
[Code] set DATEFIRST [/Code]
So, i simply attached below command before my week related queries,
[Code] set DATEFIRST 1 [/Code]
But when this started returning me unexpected results.
It gave '4 Jan 2010' starts as Second week of 2010. And First week has only 3 days i.e. '1 Jan 2010' to '3 Jan 2010'.
But what i actually wanted was:
'01 Jan 2010 to 03 Jan 2010 ' as 53rd week
'04 Jan 2010 to 10 10 Jan 2010 ' as 1st week
[Code]
declare @jobs table (d datetime)
set datefirst 7
insert into @jobs
select '12/20/2009' union all
select '12/21/2009' union all
select '12/22/2009' union all
select '12/23/2009' union all
select '12/24/2009' union all
select '12/25/2009' union all
select '12/26/2009' union all
select '12/27/2009' union all
select '12/28/2009' union all
select '12/29/2009' union all
select '12/30/2009' union all
select '12/31/2009' union all
select '01/01/2010' union all
select '01/02/2010' union all
select '01/03/2010' union all
select '01/04/2010' union all
select '01/05/2010' union all
select '01/06/2010' union all
select '01/07/2010' union all
select '01/08/2010' union all
select '01/09/2010' union all
select '01/10/2010' union all
select '01/11/2010' union all
select datepart(ww,d) as week,datename(dw,d) as day, * from @jobs
[/Code]
So, how can i get the expected results?
Thank you.
February 5, 2010 at 1:20 am
From BOL:
January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
One workaround is to subtract a number of days (specific to the year) from your argument in the datepart function.
E.g. If January 4 is the 'first' day of the 'first' week, then you need to subtract 3 from every day in that year before using the datepart function.
SELECT datepart(wk,DATEADD(dd,-3,'20100101')); -- returns 53
SELECT datepart(wk,DATEADD(dd,-3,'20100102')); -- returns 53
SELECT datepart(wk,DATEADD(dd,-3,'20100103')); -- returns 53
SELECT datepart(wk,DATEADD(dd,-3,'20100104')); -- returns 1
February 5, 2010 at 1:21 am
Use a calendar table
February 5, 2010 at 2:02 am
wschampheleer (2/5/2010)
From BOL:January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
One workaround is to subtract a number of days (specific to the year) from your argument in the datepart function.
E.g. If January 4 is the 'first' day of the 'first' week, then you need to subtract 3 from every day in that year before using the datepart function.
SELECT datepart(wk,DATEADD(dd,-3,'20100101')); -- returns 53
SELECT datepart(wk,DATEADD(dd,-3,'20100102')); -- returns 53
SELECT datepart(wk,DATEADD(dd,-3,'20100103')); -- returns 53
SELECT datepart(wk,DATEADD(dd,-3,'20100104')); -- returns 1
hi,
But -3 will work only for 2010 and for 2011 it will be -2. Is it better to do a hardcoding ?
February 5, 2010 at 2:18 am
Hold your horses Gandalf! Who said anything about hard coding?
The value to subtract can easily be calculated dynamically and integrated into the query:
SELECT DATEPART(wk,DATEADD(dd, -(@@datefirst + 7 - DATEPART(dw, DATEADD(yy, DATEDIFF(yy, 0, '20100104'), 0))) % 7, '20100104')) ; -- returns 1
I agree with Dave that implementing a calendar table is a better and cleaner solution, but sometimes you simply have not the option to create any additional tables etc. All you can do then is being a bit creative with your queries. And BTW: you still need a way to enter the correct values into your calendar table 🙂
February 5, 2010 at 3:08 am
wschampheleer (2/5/2010)
Hold your horses Gandalf! Who said anything about hard coding?The value to subtract can easily be calculated dynamically and integrated into the query:
SELECT DATEPART(wk,DATEADD(dd, -(@@datefirst + 7 - DATEPART(dw, DATEADD(yy, DATEDIFF(yy, 0, '20100104'), 0))) % 7, '20100104')) ; -- returns 1I agree with Dave that implementing a calendar table is a better and cleaner solution, but sometimes you simply have not the option to create any additional tables etc. All you can do then is being a bit creative with your queries. And BTW: you still need a way to enter the correct values into your calendar table 🙂
hi and thank you,
i tried the above command with SET DATEFIRST 1 and it gave me this result:
1 Jan 2010 to 3 Jan 2010 -- week 53
4 Jan 2010 to 6 Jan 2010 -- week 1
7 Jan 2010 to 13 Jan 2010 -- week 2
8 Jan --- and so on
is it correct that week 1 had 3 days ?
February 5, 2010 at 3:24 am
Ooops, you're right. Looks like SQL Server says week 1 starts on Jan 1 like BOL say, but week 2 then starts on the day you specified with DATEFIRST.
This behaviour is also apparent when using the function straight away:
SET DATEFIRST 1;
SELECT DATEPART(wk,'20100101'); -- returns 1
SELECT DATEPART(wk,'20100102'); -- returns 1
SELECT DATEPART(wk,'20100103'); -- returns 1
SELECT DATEPART(wk,'20100104'); -- returns 2
SELECT DATEPART(wk,'20100105'); -- returns 2
SELECT DATEPART(wk,'20100106'); -- returns 2
Week 53 is also a bit special.
28-DEC-2009 till 31-DEC-2009 belong to week 52 looked at from the 2009 perspective
but they are also part of week 53 looked at from the 2010 perspective
SET DATEFIRST 1;
SELECT DATEPART(wk,DATEADD(dd, -(@@datefirst + 7 - DATEPART(dw, DATEADD(yy, DATEDIFF(yy, 0, '20100101'), 0))) % 7, '20091231')) ; -- returns 53
SELECT DATEPART(wk,DATEADD(dd, -(@@datefirst + 7 - DATEPART(dw, DATEADD(yy, DATEDIFF(yy, 0, '20091231'), 0))) % 7, '20091231')) ; -- returns 52
To be continued...
February 5, 2010 at 3:59 am
I think you are looking for ISO week number. Check following links:
http://www.sqlservercentral.com/articles/Advanced+Querying/onthetrailoftheisoweek/1675/
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/iso-week-in-sql-server
February 5, 2010 at 4:02 am
Try this
SET DATEFIRST 1;
SELECT (DATEPART(wk, '20091225') + 50) % 52 + 1; -- returns 51
SELECT (DATEPART(wk, '20091226') + 50) % 52 + 1; -- returns 51
SELECT (DATEPART(wk, '20091227') + 50) % 52 + 1; -- returns 51
SELECT (DATEPART(wk, '20091228') + 50) % 52 + 1; -- returns 52
SELECT (DATEPART(wk, '20091229') + 50) % 52 + 1; -- returns 52
SELECT (DATEPART(wk, '20091230') + 50) % 52 + 1; -- returns 52
SELECT (DATEPART(wk, '20091231') + 50) % 52 + 1; -- returns 52
SELECT (DATEPART(wk, '20100101') + 50) % 52 + 1; -- returns 52
SELECT (DATEPART(wk, '20100102') + 50) % 52 + 1; -- returns 52
SELECT (DATEPART(wk, '20100103') + 50) % 52 + 1; -- returns 52
SELECT (DATEPART(wk, '20100104') + 50) % 52 + 1; -- returns 1
SELECT (DATEPART(wk, '20100105') + 50) % 52 + 1; -- returns 1
SELECT (DATEPART(wk, '20100106') + 50) % 52 + 1; -- returns 1
SELECT (DATEPART(wk, '20100107') + 50) % 52 + 1; -- returns 1
SELECT (DATEPART(wk, '20100108') + 50) % 52 + 1; -- returns 1
SELECT (DATEPART(wk, '20100109') + 50) % 52 + 1; -- returns 1
SELECT (DATEPART(wk, '20100110') + 50) % 52 + 1; -- returns 1
SELECT (DATEPART(wk, '20100111') + 50) % 52 + 1; -- returns 2
February 5, 2010 at 5:02 am
Hi,
Thanks Willem for taking efforts to solve the prob...
Yes i went thru the links that you provided, and i found that iactually need ISO week.
Its given in BOL a nice function i copied and pasted here
[Code]
CREATE FUNCTION ISOweek (@DATE DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @ISOweek INT
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) AS CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
GO
[/Code]
I gave my inputs and checked the results and its the same thing that i wanted.
Now i created above function in my DB.
The earlier query that i used was something like this,
[Code]
select
datepart(yy,date) as [Year],
datepart(ww,date) as [Week],
Avg(Prices) as [Prices]
from tblPrices
group by datepart(yy,date),datepart(ww,date)
[/Code]
How do i fit in that ISOWeek function with this query ?
Thanks & Kind regards,
February 5, 2010 at 5:10 am
February 5, 2010 at 5:22 am
Thank you very very much Willem. You have solved it !
i used below code and it worked ,
[Code]
set datefirst 1
select
datepart(yy,date) as [Year],
dbo.ISOweek(date) as [Week],
Avg(Prices) as [Prices]
from tblPrices
group by datepart(yy,date),dbo.ISOweek(date)
[/Code]
Finally , I have about 2 lakh of records in the above table, will there be any efficiency issues with this ?
Thanks & Kind regards.
February 5, 2010 at 5:51 am
I first had to lookup what lakh means - lucky there is Google 🙂 and Wikipedia :-))
So you have about 200.000 records, right?
Scalar funcions are notorious for causing performance problems if not used judiciously - you may be interested in this thread - but in your case I do not expect any problem.
February 5, 2010 at 8:59 pm
thank you.
February 8, 2010 at 2:21 am
🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply