Post reply

Week of the month

  • May 17, 2012 at 3:19 am

    #256640

    Hi all,

    how can i get a week of the month given a specified date?

    my week start on Monday and end on Sunday.

    Regards

  • May 17, 2012 at 3:23 am

    #1488857

    This should do the trick, also dont forget the power of Google or your favourite search engine

    set datefirst 1

    DECLARE @dt DATETIME, @WeekOfMonth TINYINT

    SET @dt = '2007-07-08'

    SET @WeekOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0), @dt) +1

    PRINT @WeekOfMonth

  • May 17, 2012 at 3:27 am

    #1488858

    Thank you so much.

    Regards

  • May 17, 2012 at 8:37 am

    #1489064

    anthony.green (5/17/2012)

    This should do the trick, also dont forget the power of Google or your favourite search engine

    set datefirst 1

    DECLARE @dt DATETIME, @WeekOfMonth TINYINT

    SET @dt = '2007-07-08'

    SET @WeekOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0), @dt) +1

    PRINT @WeekOfMonth

    Unfortunately, DATEDIFF ignores the setting for DATEFIRST.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • May 17, 2012 at 8:40 am

    #1489067

    Thanks Drew, learn something new every day.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply