Post reply

How to get the las week number of the current month

  • December 12, 2016 at 1:52 am

    #332675

    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

  • December 12, 2016 at 2:08 am

    #1917314

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

    John

  • December 12, 2016 at 3:50 am

    #1917331

    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 ,

  • December 12, 2016 at 4:18 am

    #1917335

    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

  • December 12, 2016 at 4:23 am

    #1917336

    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