How to get currect number of weeks for the given date considering when 31st december is saturday
-
Hi, I have a situation where I wanted to have the weekno for the given date. If year is changing then weekno should add with the previous weekno. Below is the table contains two columns, Bug date and corresponding weeknumber... till 12/31/2011 I am getting correctly but after that am getting wrong result. for 01/01/2012 the weeknumber should be 158 but am getting 157, can any one help me solve this problem..Pls see my logic below... am using sql server 2008. Bug Date weeknumber 10/25/2009 44 11/01/2009 45 12/30/2009 53 12/31/2009 53 01/01/2010 53 01/03/2010 54 12/30/2010 105 12/31/2010 105 01/01/2011 105 01/02/2011 106 01/04/2011 106 12/30/2011 157 12/31/2011 157 01/01/2012 158 01/08/2012 158 DECLARE @FirstDate char(20) DECLARE @baseyear char(20) SET @FirstDate =(SELECT Min(Bugdate) FROM BugsDB_DefectVolume) SET @baseyear=(SELECT Datepart(year,@FirstDate)) //am considering the first date as base year. UPDATE BugsDB_DefectVolume SET weeknumber= Datepart(wk,@BugDate) + ((Datepart(year,@BugDate) - @baseyear) *52), StartDayofWeek=DATEadd(day, (1- DATEPART(dw,@BugDate)), @BugDate) WHERE BugDate=@BugDate and [Product ID]=@ProductID
-
Hi, I have a situation where I wanted to have the weekno for the given date. If year is changing then weekno should add with the previous weekno. Below is the table contains two columns, Bug date and corresponding weeknumber... till 12/31/2011 I am getting correctly but after that am getting wrong result. for 01/01/2012 the weeknumber should be 158 but am getting 157, can any one help me solve this problem..Pls see my logic below... am using sql server 2008. Bug Date weeknumber 10/25/2009 44 11/01/2009 45 12/30/2009 53 12/31/2009 53 01/01/2010 53 01/03/2010 54 12/30/2010 105 12/31/2010 105 01/01/2011 105 01/02/2011 106 01/04/2011 106 12/30/2011 157 12/31/2011 157 01/01/2012 158 01/08/2012 158 DECLARE @FirstDate char(20) DECLARE @baseyear char(20) SET @FirstDate =(SELECT Min(Bugdate) FROM BugsDB_DefectVolume) SET @baseyear=(SELECT Datepart(year,@FirstDate)) //am considering the first date as base year. UPDATE BugsDB_DefectVolume SET weeknumber= Datepart(wk,@BugDate) + ((Datepart(year,@BugDate) - @baseyear) *52), StartDayofWeek=DATEadd(day, (1- DATEPART(dw,@BugDate)), @BugDate) WHERE BugDate=@BugDate and [Product ID]=@ProductID
Does this work:
DECLARE @FirstDate char(20)
SET @FirstDate =(SELECT Min(Bugdate) FROM BugsDB_DefectVolume)
UPDATE BugsDB_DefectVolume
SET weeknumber= DateDiff(wk, @FirstDate, @BugDate) +1,
StartDayofWeek=DATEadd(day, (1- DATEPART(dw,@BugDate)), @BugDate)
WHERE BugDate=@BugDate and [Product ID]=@ProductIDWout Louwers
-
Does this work:
DECLARE @FirstDate char(20)
SET @FirstDate =(SELECT Min(Bugdate) FROM BugsDB_DefectVolume)
UPDATE BugsDB_DefectVolume
SET weeknumber= DateDiff(wk, @FirstDate, @BugDate) +1,
StartDayofWeek=DATEadd(day, (1- DATEPART(dw,@BugDate)), @BugDate)
WHERE BugDate=@BugDate and [Product ID]=@ProductIDWout Louwers
-
Hi, I have a situation where I wanted to have the weekno for the given date. If year is changing then weekno should add with the previous weekno. Below is the table contains two columns, Bug date and corresponding weeknumber... till 12/31/2011 I am getting correctly but after that am getting wrong result. for 01/01/2012 the weeknumber should be 158 but am getting 157, can any one help me solve this problem..Pls see my logic below... am using sql server 2008. Bug Date weeknumber 10/25/2009 44 11/01/2009 45 12/30/2009 53 12/31/2009 53 01/01/2010 53 01/03/2010 54 12/30/2010 105 12/31/2010 105 01/01/2011 105 01/02/2011 106 01/04/2011 106 12/30/2011 157 12/31/2011 157 01/01/2012 158 01/08/2012 158 DECLARE @FirstDate char(20) DECLARE @baseyear char(20) SET @FirstDate =(SELECT Min(Bugdate) FROM BugsDB_DefectVolume) SET @baseyear=(SELECT Datepart(year,@FirstDate)) //am considering the first date as base year. UPDATE BugsDB_DefectVolume SET weeknumber= Datepart(wk,@BugDate) + ((Datepart(year,@BugDate) - @baseyear) *52), StartDayofWeek=DATEadd(day, (1- DATEPART(dw,@BugDate)), @BugDate) WHERE BugDate=@BugDate and [Product ID]=@ProductID
It's because contrary to popular belief, there are not 52 weeks in a year. There are 52 weeks and one spare day left over. So, if you just assume 52 weeks every year, eventually your calculation gets out of line. That's why financial years occasionally have a week 53 to stop things from drifting off. Basically about 1 year in every 5 or 6 has 53 weeks. It's not a straight 1 in 7 because leap years throw it out (every fourth year has 52 weeks and 2 days left over which makes the calculation trickier). There is an ISO standard which defines it. 2004 had 53 weeks, 2009 has 53 weeks, 2010 has 52, the next 53 week year will be 2015. Basically, if 1st Jan is a Thursday (or if it is a leap year either a Wednesday or a Thursday) then there are 53 weeks in the year. For what you want, you might be able to calculate the number of days from a base date and then divide by 7. It might be easier than trying to work out weeks per year. Does that give the right answer?
-
Yes its working fine but only the problem is when date is 01/01/2012 its giving the weeknumber as 157. this is wrong. it should be 158. To achieve this where can I do the modification in my existing query???
-
It's because contrary to popular belief, there are not 52 weeks in a year. There are 52 weeks and one spare day left over. So, if you just assume 52 weeks every year, eventually your calculation gets out of line. That's why financial years occasionally have a week 53 to stop things from drifting off. Basically about 1 year in every 5 or 6 has 53 weeks. It's not a straight 1 in 7 because leap years throw it out (every fourth year has 52 weeks and 2 days left over which makes the calculation trickier). There is an ISO standard which defines it. 2004 had 53 weeks, 2009 has 53 weeks, 2010 has 52, the next 53 week year will be 2015. Basically, if 1st Jan is a Thursday (or if it is a leap year either a Wednesday or a Thursday) then there are 53 weeks in the year. For what you want, you might be able to calculate the number of days from a base date and then divide by 7. It might be easier than trying to work out weeks per year. Does that give the right answer?
And you got down voted for that answer - FTFY
Never underestimate the power of human stupidity RAH