SQL Server Performance

Week in specific month

Discussion in 'General Developer Questions' started by Madhivanan, Jun 6, 2007.

  1. Madhivanan Moderator


    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
  2. Adriaan New Member

    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
  3. Adriaan New Member

    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
  4. Madhivanan Moderator

    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
  5. Adriaan New Member

    Exactly. No shortcuts here.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  6. Madhivanan Moderator

    <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
  7. FrankKalis Moderator

    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>
  8. Madhivanan Moderator

    Frank, I dont understand
    What is the conclusion?

    Madhivanan

    Failing to plan is Planning to fail
  9. Adriaan New Member

    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.
  10. FrankKalis Moderator

    <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>

Share This Page