How to get the las week number of the current month

  • hi

    I need to get the last week number of the current month.

    e.g '20161201' to '20161231' consists of WeekNumber 49 to 53

    so I need to return week 53 when selecting '20161201' to '20161231'

    Regards

  • Have you tried the DATENAME function? You can choose week or iso_week as the datepart.

    John

  • hi John

    No I haven't

    what I need to do is when the user select the date range: 20161201 to 20161231 I want to be able to return data for 20161225 to 20161231 since it's the last week of the month.

    regards ,

  • So you always want to return the last seven day period in the month, regardless of what day of the week it starts on?DECLARE @Date datetime;

    DECLARE @StartofNextMonth datetime;

    SET @Date = CURRENT_TIMESTAMP;

    SET @StartofNextMonth = DATEADD(month,DATEDIFF(month,1,@Date)+1,0)

    SELECT

    DATEADD(day,-7,@StartofNextMonth) AS StartofLastWeek

    ,DATEADD(day,-1,@StartofNextMonth) AS EndofLastWeek;

    John

  • Thank you so much

    It worked as expected.

    Regards

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

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