Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2004
    Location
    L.A
    Posts
    19

    Unanswered: How to get "week of the month"?

    This seems like a difficult one ..

    I am trying to calculate the number of the week within a month of a given date. in other words, given a date I need to find out whether it's the 1st week or 2nd or 3rd or 4th or 5th week of that given month.

    for instance let's call my function "weekOfMonth()":

    weekOfMonth(4/19/2004) should return 4 (it's the fourth week of april)
    weekOfMonth(4/7/2004) should return 2 (it's the second week of april)
    etc ..

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about:

    Code:
    DECLARE @x datetime, @y datetime, @z datetime 
    SELECT @x = GetDate()
    SELECT @y = CONVERT(datetime,CONVERT(varchar(2),DATEPART(m,@x))+'/01/'+CONVERT(varchar(4),DATEPART(yy,@x)))
    SELECT @z = DATEADD(d,-1
    		,DATEADD(m,1,CONVERT(datetime,CONVERT(varchar(2),DATEPART(m,@x))
    		+'/01/'+CONVERT(varchar(4),DATEPART(yy,@x)))))
    
    SELECT (DATEPART(wk,@z)-1)-DATEPART(wk,@y)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This opens a very large can of worms. How do you define "week of the month" ? Do you count the 1-7, 8-14,15-21 days? Do you always start the week on a Monday? Is the week of the month related to the week of the year?

    Check out the ISO pages for discussions of time and the issues related to it. They get really, REALLY ugly!

    For a rude, crude, and rather simplistic solution, you could use:
    PHP Code:
    CREATE FUNCTION dbo.f_WeekOfMonth(
       @
    d DATETIME
    )  RETURNS INT AS
       
    BEGIN
          
    RETURN DateDiff(weekConvert(CHAR(8), @d121) + '01', @d)
       
    END
    GO 
    -PatP
    Last edited by Pat Phelan; 04-19-04 at 13:15.

  4. #4
    Join Date
    Mar 2004
    Location
    L.A
    Posts
    19
    Brett,
    your solution returns 3 for today's date (4/19/2004)

    Pat,
    Your solution seems to work fine with the examples that I tried ...

    For this function, I am considering Monday as the first day of the week
    and Sunday the last day ..

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Gotta tweak it...but isn't this the 3rd week?

    My Calendar says so

    But Datepart(wk, '4/30/2004') gives me 19, whereas my calendar say 17

    What gives....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Huh?

    Code:
    SELECT COUNT(DISTINCT week_no) FROM (
    select datepart(wk,'4/1/2004') as week_no UNION ALL
    select datepart(wk,'4/5/2004') as week_no UNION ALL
    select datepart(wk,'4/12/2004') as week_no UNION ALL
    select datepart(wk,'4/19/2004') as week_no UNION ALL
    select datepart(wk,'4/26/2004') as week_no UNION ALL
    select datepart(wk,'4/30/2004') as week_no)AS XXX
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    ISO standard weeks start on Sunday, and the year usually starts with week 1 (there are some funky rules there).

    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Anyone want to share what week # they think this is?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Looking for a much bigger can now, are we?

    Ok, which week in whose calendar, using which of the available standards? ISO says it is week 17 of year 2004. Unix says it is week 1791. Shall we go on?

    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Pat Phelan
    Looking for a much bigger can now, are we?

    Ok, which week in whose calendar, using which of the available standards? ISO says it is week 17 of year 2004. Unix says it is week 1791. Shall we go on?

    -PatP

    I actually meant 1-4 (or 5)

    ISO say 17, my calendar says 16...

    But it's still the third week...no?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    CREATE FUNCTION dbo.udf_WeekOfMonth(
       @d DATETIME
    )  RETURNS INT AS
       BEGIN
    	DECLARE @y datetime
    	 SELECT @y = CONVERT(datetime,CONVERT(varchar(2),DATEPART(m,@d))+'/01/'+CONVERT(varchar(4),DATEPART(yy,@d)))
    	RETURN DATEPART(wk,@d)-DATEPART(wk,@y)     
       END
    GO
    
    SELECT dbo.udf_WeekOfMonth('4/19/2004')
    SELECT dbo.udf_WeekOfMonth('6/14/2004')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    --Depending upon what you want...

    --Calendar week of the month:
    select datediff(week, convert(char(7), Getdate(), 120)+ '-01', Getdate())+1

    --Weeks since start of month:
    select (datediff(day, convert(char(7), Getdate(), 120)+ '-01', Getdate())/7)+1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Mar 2004
    Location
    Venice,Italy
    Posts
    20
    my 2 cents:
    I had the problem to test something looks like "third monday of the month";
    I resolved:
    select * from tabella where datepart(dw,day_test)=1 and ceiling(datepart(dd,day_test)/7)=3
    (I'm in Italy and here week starts on monday...)

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by zeus77
    my 2 cents:
    I had the problem to test something looks like "third monday of the month";
    I resolved:
    select * from tabella where datepart(dw,day_test)=1 and ceiling(datepart(dd,day_test)/7)=3
    (I'm in Italy and here week starts on monday...)
    I think that my solution works nicely if you use SET DATEFIRST, doesn't it?

    -PatP

  15. #15
    Join Date
    Mar 2004
    Location
    L.A
    Posts
    19
    Pat, your solution (the same as blindman's) works perfect ..

    Brett's solution returns 3 for the '04/19/2004' ... it should be 4 because I am looking for the number of calendar week ..

    Thank you guys ..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •