Select datepart(week,'2007-04-11') will give week number starting from first day of that year 15 If I want week number for that specific month, will this be fine? Select datepart(week,dateadd(day,day('2007-04-11'),0)) 2 Madhivanan Failing to plan is Planning to fail
You're determining the week for the expression DATEADD(DAY, DAY('2007-04-11'), 0) which is actually ... SELECT DATEADD(DAY, DAY('2007-04-11'), 0) ------------- 1900-01-12 00:00:00.000 If you check the weekday, you'll see that this is not a valid method: SELECT DATEPART(week, DATEADD(day,day(x.mydate),0)), x.mydate, DATENAME(dw, x.mydate) FROM (select '2007-04-01' mydate union select '2007-04-02' union select '2007-04-03' union select '2007-04-04' union select '2007-04-05' union select '2007-04-6' union select '2007-04-07') x Let's look at the results: 12007-04-01 00:00:00.000Sunday 12007-04-02 00:00:00.000Monday -- Monday should start week 2 12007-04-03 00:00:00.000Tuesday 12007-04-04 00:00:00.000Wednesday 12007-04-05 00:00:00.000Thursday 22007-04-06 00:00:00.000Friday -- the new week does not start on Friday 22007-04-07 00:00:00.000Saturday
And if you're just looking to see if you're between days 1-7, 8-14, 15-21, 22-28 or 29-31, then your method is also off - look at 2007-04-06
quote:Originally posted by Adriaan And if you're just looking to see if you're between days 1-7, 8-14, 15-21, 22-28 or 29-31, then your method is also off - look at 2007-04-06 Yes. This is what I want. I think case will do the thing declare @date datetime select @date='2007-04-14' select case when day(@date) between 1 and 7 then 1 when day(@date) between 8 and 14 then 2 when day(@date) between 15 and 21 then 3 when day(@date) between 22 and 28 then 4 else 5 end Madhivanan Failing to plan is Planning to fail
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Exactly. No shortcuts here.[<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I thought there should be [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
I think there might be a pitfall here. When I look at my calendar in Outlook it is showing me that 2007-04-14 is in the 25th week in 2007 and in the 3rd week in April. <s>So the CASE expression you've posted is only then determining the week in a given month when the first day of that month happens to be the same day as defined by SET DATEFIRST, which isn't always the case</s>. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Using Adriaan's example, this should be more reliable:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT DATEPART(WEEK, DATEADD(DAY, @@datefirst - 1, x.mydate)) <br /> - DATEPART(WEEK, DATEADD(DAY, @@datefirst - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, x.mydate), 0))) + 1<br /> , x.mydate, DATENAME(dw, x.mydate)<br />FROM <br />(select '20070407' mydate union select '20070408' union select '20070409' union select '20070410' <br />union select '20070411' union select '20070412' union select '20070413' union select '20070414') x<br /><br /> mydate <br />----------- -------- ------------------------------<br />2 20070407 Samstag<br />2 20070408 Sonntag<br />3 20070409 Montag<br />3 20070410 Dienstag<br />3 20070411 Mittwoch<br />3 20070412 Donnerstag<br />3 20070413 Freitag<br />3 20070414 Samstag<br /><br />(8 row(s) affected)<br /></font id="code"></pre id="code"><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Frank,<br /><br />Madhivanan's second post shows he's just going by the day-of-the-month, so 20070407 is still in the first week, not the second.[<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><br />I also notice that our SQL Server instances all have the U.S. English default @@DATEFIRST setting, which is 7 for Sunday. In Europe, we usually go by Monday, but the SQL installer doesn't seem to bother with such niceties.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Frank, I dont understand<br />What is the conclusion?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />The conclusion is that the 7th day isn't necessarily in the 1 week of that month. So, you might need to be aware that the results based on such a query are probably skewed. I find it also more intuitive to say something like: "The 1st 2 weeks of a month" than to say: "The 1st 14 days". [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
