how to get week number and startday of the week in sql server 2008
-
Hi, I am struggling to get the week number and week day of the particular day. I know we can use Datepart function which is available in sql server 2008 to display the week number of the given date. but my situation is different. for example Datepart(wk,'2009-12-30') gives 53 weeks Datepart(wk,'2010-01-02') gives 1. but I wanted to display it as 54. whenever the year is changing I need to add these week number to the previous year one. In the below table Bugdate is the input, and weeknumber and startday of the week are the expected outputs. BugDate ----- weeknumber ---- Startday of the Week .... 12/13/2008---- 50------------- 12/7/2008 12/14/2008---- 51------------- 12/14/2008 12/21/2008---- 52------------- 12/21/2008 12/23/2008---- 52------------- 12/21/2008 12/30/2008---- 53------------- 12/28/2008 1/2/2009------- 54------------- 12/28/2008 1/6/2009------- 55------------- 1/4/2009 ... 12/20/2009---- 105------------ 12/20/2009 12/28/2009---- 106------------ 12/27/2009 1/1/2010------ 106------------ 12/27/2009 Can any one help me to solve this problem. Thanks in advance.
-
Hi, I am struggling to get the week number and week day of the particular day. I know we can use Datepart function which is available in sql server 2008 to display the week number of the given date. but my situation is different. for example Datepart(wk,'2009-12-30') gives 53 weeks Datepart(wk,'2010-01-02') gives 1. but I wanted to display it as 54. whenever the year is changing I need to add these week number to the previous year one. In the below table Bugdate is the input, and weeknumber and startday of the week are the expected outputs. BugDate ----- weeknumber ---- Startday of the Week .... 12/13/2008---- 50------------- 12/7/2008 12/14/2008---- 51------------- 12/14/2008 12/21/2008---- 52------------- 12/21/2008 12/23/2008---- 52------------- 12/21/2008 12/30/2008---- 53------------- 12/28/2008 1/2/2009------- 54------------- 12/28/2008 1/6/2009------- 55------------- 1/4/2009 ... 12/20/2009---- 105------------ 12/20/2009 12/28/2009---- 106------------ 12/27/2009 1/1/2010------ 106------------ 12/27/2009 Can any one help me to solve this problem. Thanks in advance.
You could do something like Select Datepart(wk,'2010-01-02') + ((Datepart(yr,'2010-01-02') - 2009) * 52 OR get the datediff days / 7 and use floor or ceiling to get the number you want (this is more reliable as the week numbers per year do not change. This is a fairly simple exercise of thinking around corners, you are not flexing you brain enough.
Never underestimate the power of human stupidity RAH
-
You could do something like Select Datepart(wk,'2010-01-02') + ((Datepart(yr,'2010-01-02') - 2009) * 52 OR get the datediff days / 7 and use floor or ceiling to get the number you want (this is more reliable as the week numbers per year do not change. This is a fairly simple exercise of thinking around corners, you are not flexing you brain enough.
Never underestimate the power of human stupidity RAH
-
Hi, Thanks for your reply to get the week number. But how to get the first day of the week for the given date? Thanks,
Read up on datepart in BOL, there is a dayofweek element that will tell you.
Never underestimate the power of human stupidity RAH
-
Hi, I am struggling to get the week number and week day of the particular day. I know we can use Datepart function which is available in sql server 2008 to display the week number of the given date. but my situation is different. for example Datepart(wk,'2009-12-30') gives 53 weeks Datepart(wk,'2010-01-02') gives 1. but I wanted to display it as 54. whenever the year is changing I need to add these week number to the previous year one. In the below table Bugdate is the input, and weeknumber and startday of the week are the expected outputs. BugDate ----- weeknumber ---- Startday of the Week .... 12/13/2008---- 50------------- 12/7/2008 12/14/2008---- 51------------- 12/14/2008 12/21/2008---- 52------------- 12/21/2008 12/23/2008---- 52------------- 12/21/2008 12/30/2008---- 53------------- 12/28/2008 1/2/2009------- 54------------- 12/28/2008 1/6/2009------- 55------------- 1/4/2009 ... 12/20/2009---- 105------------ 12/20/2009 12/28/2009---- 106------------ 12/27/2009 1/1/2010------ 106------------ 12/27/2009 Can any one help me to solve this problem. Thanks in advance.
Try this . declare @tbl table(bugdate date) insert into @tbl select '12/13/2008' union all select '12/14/2008' union all select '12/21/2008' union all select '12/23/2008' union all select '12/30/2008' union all select '1/2/2009' union all select '1/6/2009' union all select '12/20/2009' union all select '12/28/2009' union all select '1/1/2010'
select bugdate,
case when Datepart(yy,bugdate) = 2009 then Datepart(wk,bugdate) + 53*1
when Datepart(yy,bugdate) = 2010 then Datepart(wk,bugdate) + 53*2
else Datepart(wk,bugdate)
end as weeknumber
,DATEPART(dw,bugdate) weekdays
,DATENAME(dw, DATEPART(dw,bugdate)) weekdayname
,DATEadd(day, (1- DATEPART(dw,bugdate)), bugdate) AS startweek
,DATENAME(dw, DATEadd(day, (1- DATEPART(dw,bugdate)), bugdate)) startweekdayname
from @tblOutput:
bugdate weeknumber weekdays weekdayname startweek startweekdayname
2008-12-13 50 7 Monday 2008-12-07 Sunday
2008-12-14 51 1 Tuesday 2008-12-14 Sunday
2008-12-21 52 1 Tuesday 2008-12-21 Sunday
2008-12-23 52 3 Thursday 2008-12-21 Sunday
2008-12-30 53 3 Thursday 2008-12-28 Sunday
2009-01-02 54 6 Sunday 2008-12-28 Sunday
2009-01-06 55 3 Thursday 2009-01-04 Sunday
2009-12-20 105 1 Tuesday 2009-12-20 Sunday
2009-12-28 106 2 Wednesday 2009-12-27 Sunday
2010-01-01 107 6 Sunday 2009-12-27 SundayDepending on ur requirement, accept the columns.
Note that I have multiplied 53 * 1 for 2009 & 53*2 for 2010. Now why 53? Because if you want to find out the last week of the last day of a year it will be 53. Try this Select Datepart(wk,'12/31/2008') or Select Datepart(wk,'12/31/2009') etc. The output will be 53. I am considering 2008(here) as my starting point and henceforth nothing to add with the weeks for this year. Since year 2009 is 1 ahead so I am multiplying 53 with 1(which is though of no use) and adding that to every week of that year(2009). The same rule apply for 2010 with the difference that 53 will be multiplied with 2. If we follow this rule then we can generalized a formula 53 * n where n=0,1....Note- Follow what Mr.Mycroft said. It is better for ur learning. :)Niladri Biswas
modified on Friday, October 30, 2009 5:03 AM
-
You could do something like Select Datepart(wk,'2010-01-02') + ((Datepart(yr,'2010-01-02') - 2009) * 52 OR get the datediff days / 7 and use floor or ceiling to get the number you want (this is more reliable as the week numbers per year do not change. This is a fairly simple exercise of thinking around corners, you are not flexing you brain enough.
Never underestimate the power of human stupidity RAH
Hi, the query is failing in one situation. eg, when dates are '2011-12-31' '2012-01-01', this case returning same week number for both the dates but this is wrong. For '2012-01-01' this is the start of the week so it is increment of the previous date week... Date-------- weekNumber 2011-12-31-- 157 2012-01-01-- 157(wrong result it should be 158) 2012-01-02-- 157(wrong result it should be 158) Thanks..
-
Hi, the query is failing in one situation. eg, when dates are '2011-12-31' '2012-01-01', this case returning same week number for both the dates but this is wrong. For '2012-01-01' this is the start of the week so it is increment of the previous date week... Date-------- weekNumber 2011-12-31-- 157 2012-01-01-- 157(wrong result it should be 158) 2012-01-02-- 157(wrong result it should be 158) Thanks..
But as per the query which I have given it should be Date-------- weekNumber 2011-12-31-- 212 and not 157 2012-01-01-- 213 and not 157 or 158 2012-01-02-- 213 and not 157 or 158 And it is correct only. Even I calculated the value. Only change the case statement by the following lines
case when Datepart(yy,bugdate) = 2009 then Datepart(wk,bugdate) + 53*1
when Datepart(yy,bugdate) = 2010 then Datepart(wk,bugdate) + 53*2
when Datepart(yy,bugdate) = 2011 then Datepart(wk,bugdate) + 53*3
when Datepart(yy,bugdate) = 2012 then Datepart(wk,bugdate) + 53*4
else Datepart(wk,bugdate)
end as weeknumber:)
Niladri Biswas
-
But as per the query which I have given it should be Date-------- weekNumber 2011-12-31-- 212 and not 157 2012-01-01-- 213 and not 157 or 158 2012-01-02-- 213 and not 157 or 158 And it is correct only. Even I calculated the value. Only change the case statement by the following lines
case when Datepart(yy,bugdate) = 2009 then Datepart(wk,bugdate) + 53*1
when Datepart(yy,bugdate) = 2010 then Datepart(wk,bugdate) + 53*2
when Datepart(yy,bugdate) = 2011 then Datepart(wk,bugdate) + 53*3
when Datepart(yy,bugdate) = 2012 then Datepart(wk,bugdate) + 53*4
else Datepart(wk,bugdate)
end as weeknumber:)
Niladri Biswas
the problem with your query is if the years will be increased in the future. so instead of adding condition many times we need to summarize that. second is --Date----- weeknumber 2008-12-30--- 53 2009-01-02--- 54( this is wrong it should be 53, it falls in the 53rd week) Thanks,
-
the problem with your query is if the years will be increased in the future. so instead of adding condition many times we need to summarize that. second is --Date----- weeknumber 2008-12-30--- 53 2009-01-02--- 54( this is wrong it should be 53, it falls in the 53rd week) Thanks,
Come on. it is correct. Because if you execute select Datepart(wk,'12/30/2008') you will get the weeknumber as 53 and select Datepart(wk,'1/2/2009') will yield 1. As per your requirement, which you specified it should be 54. and henceforth the result. For the first part(if the years will be increased in the future. so instead of adding condition many times we need to summarize that) we need to work on. :)
Niladri Biswas
-
Come on. it is correct. Because if you execute select Datepart(wk,'12/30/2008') you will get the weeknumber as 53 and select Datepart(wk,'1/2/2009') will yield 1. As per your requirement, which you specified it should be 54. and henceforth the result. For the first part(if the years will be increased in the future. so instead of adding condition many times we need to summarize that) we need to work on. :)
Niladri Biswas
sorry,I am wrong, the out should be 53 not 54. because 1/2/2009 falls in week 53. Is it possible to modify the query to meet my requirement... The reply given by 'Mycroft Holmes' is working perfect but there is a small problem in that query, I posted the same in earlier thread. Thanks,
-
sorry,I am wrong, the out should be 53 not 54. because 1/2/2009 falls in week 53. Is it possible to modify the query to meet my requirement... The reply given by 'Mycroft Holmes' is working perfect but there is a small problem in that query, I posted the same in earlier thread. Thanks,
Please provide a sample output which should be correct enough. Otherwise it is becoming difficult for me to understand ur requirement. Also u give a shot. As I told u that u can learn better if u try by urself. :)
Niladri Biswas
-
Please provide a sample output which should be correct enough. Otherwise it is becoming difficult for me to understand ur requirement. Also u give a shot. As I told u that u can learn better if u try by urself. :)
Niladri Biswas
Hi, Bugdate is the input and startweek, weeknumber are expected output. when date is 2009-01-02 weeknumber should be 53 and when date is 2012-01-01 week number should be 210. BugDate Startweek WeekNumber ---------------------------------------- 2008-12-13 2008-12-07 50 2008-12-14 2008-12-14 51 2008-12-21 2008-12-21 52 2008-12-23 2008-12-21 52 2008-12-30 2008-12-28 53 2009-01-02 2008-12-28 53 2009-12-20 2009-12-20 104 2009-12-28 2009-12-27 105 2010-01-01 2009-12-27 105 2011-12-31 2011-12-25 209 2012-01-01 2012-01-01 210 2012-01-02 2012-01-01 210 2012-01-09 2012-01-08 211