|
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
|
|
|
|
|
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,
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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 @tbl
Output:
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 Sunday
Depending on ur requirement, accept the columns.
Note that I have multiplied 53 * 1 for 2009 & 53*2 for 2010. Now why 53?<br />
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....<br />
Note- Follow what Mr.Mycroft said. It is better for ur learning.
Niladri Biswas
modified on Friday, October 30, 2009 5:03 AM
|
|
|
|
|
how to apply this in a simple way
|
|
|
|
|
Click apply?
Wout Louwers
|
|
|
|
|
 What do you mean?
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
Rockery - how to apply this without sweating. You're question makes as much sense.
I suggest you do some research, possibly get a minimal understanding (a little more than this question implies) of the subject.
Get hold of SQL Express, make sure you do not have 2005 on your machine and try to install it. Or even get Access from Office professional and play with that.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There are many ways to do this. For smaller projects you can use SQLite which is free. You can also use Microsoft Access. They are both good for single user use. If you need a server, then you can try MySQL (also free). If you have the money, you can buy a SQL Server (from Microsoft) or Oracle. There are a few other free database servers available.
You probably need to write some programs to interact with the database. SQL is also a good thing to learn, although some people can get by without knowing SQL. I personally know a few who depend on the GUI of Access to do query stuff. You can write programs using VB.NET, C#, C++, or many other languages (such as Perl and PHP).
Hope this answers your question.
|
|
|
|
|
LIQUID NITROGEN
The woods are lovely, dark, and deep
But I have promises to keep
and lines to code before I sleep,
and lines to code before I sleep...
|
|
|
|
|
To quote from the site[^];
SQL Server 2005 Express for Beginners: This video series is designed specifically for SQL Server beginners-individuals who are interested in learning the basics of how to create, manage, and connect to SQL Server Express databases.
There are more short tutorials available on the web, and you can always come back here if you get stuck
I are Troll 
|
|
|
|
|
Having successfully installed SQL Server 2008 Express on the server, I found no way to connect to it from the client. "Connection refused" was the message, despite being an Administrator on both machines, and having all protocols enabled. Since I have the Management Studio for SSEx2005 installed on the client, I thought there might be a problem trying to connect because of the version difference. I tried installing the latest 2008 version on the client. No joy there - it won't install if 2005 is present, and the installer advised me to uninstall the older version first.
I did so, then re-ran the installer. Again, failure. The Performance Counters are corrupted - see this article; blah, blah. I checked the article, and it was indeed informative! But the message at the top of the page told me that this procedure doesn't apply to my OS (WinXPSP2). It referred me to the WinXP support page, which contains no equivalent article. That's not a bit surprising from my 15 years experience with Microsoft support.
Since the Web Platform Installer keeps a running account of installation attempts, and subsequent tries resulted in instantaneous failure notices, I decided to wipe the slate clean, hunted down and deleted the log files, and tried again. Another failure, this time because I "have to install SP3 for this product" or some such message. The system requirements page lists SP2 as the highest required for the package.
I've now spent 30 - 40 hours trying to install a product that is intended to lure beginners into buying the whole hog, presumably because it is so easy and useful to use. In the process I've now damaged VS2008 (which installed the 2005 version in the first place) on a virgin XP machine with no customizations - I just allowed the MS defaults when I installed the OS on a clean PC.
All I want to do is create a simple program to track inventory items and the maintenance records for them. But it's become a career just to install the tools to do so. I thought it would be nice to use the latest version of SQL Server to implement the program, but it's looking like that was a big mistake. Now my VS2008 is crippled, with no DB installed, and I have a perfectly functional, but unreachable DB engine running on the server.
By the way, the client has tons of RAM, but very little drive space and no way to improve that situation. The server has a bunch of drive space, but limited RAM, and VS2008 doesn't seem to like Windows Server 2003 very much. That's the reason for the current configuration.
Can anyone suggest a reasonable way out of this dilemma?
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Hello i want to write a where clause that will allow me to separate a table according to different column value. I do not know the values of the column because they are dynamic. I want something else than using a cursor.
|
|
|
|
|
Have a little Google[^]mojo
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would have replied in a bitter way but the way you gave me the result it was so funny that i took the link  thanks , btw i searched but i did not use the same words u did
|
|
|
|
|
That's why it is called mojo, comes from experience and having been down that path before. Good on you for taking the info and getting some value out of it, a lesser person would not have got past the implied insult.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can I get back to you in a couple of days on the topic?
I are Troll
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
