SQL SERVER – Find Monday of the Current Week

Very Simple Script which find Monday of the Current Week

SELECT
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Book Review – Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes
Next Post
SQLAuthority News – Principles of Simplicity

Related Posts

44 Comments. Leave new

  • I need to find MondayOfCurrentWeek for last year

    Reply
  • I need to find Sunday, three months ago.

    Reply
  • Thanks! Worked fine.

    Reply
  • Hi there,
    i would like to find Saturday of this week.. do u have any idea?

    Reply
  • Prakash Subramani
    August 14, 2013 10:21 am

    hi..,
    I have a doubt if i give one date and how to find Week of the Particular date ,
    and How to find that date of month and Week of the month for given date
    please do needful

    thanks in advance..
    Prakash

    Reply
  • Trying to understand how it works

    Reply
    • Because second parameter in DATEDIFF is datetime but int is used in here; and the third parameter for DATEADD is datetime but int is used in here. Could you please explain how it works

      Reply
  • My input is YEAR and WEEK number, and I need the monday of this week.

    ISO_WEEK:
    when 1 jan is friday-saturday-sunday then the 1st week starts on the next monday, and 1 jan is on 53th week of the last year.
    example 2016.01.01 is 53th week of 2015 and 1st week starts with 04 jan (monday)

    — input:
    declare @Year int = 2016
    , @Week int = 1

    — internal
    declare @Year0101 date
    , @FirstMonday date
    set datefirst 1 — monday

    select @Year0101=convert(date,convert(varchar,@year)+’.01.01′)
    select @FirstMonday=dateadd(day,-1*(datepart(WEEKDAY,@Year0101)-1),@Year0101)
    select dateadd(week,@week-1+(case when datepart(ISO_WEEK,@Year0101)=1 then 0 else 1 end),@FirstMonday)

    Reply
  • Whan You want to us it in function, then You can’t change DateFirst, use this:
    — input
    declare @Year int = 2016
    , @Week int = 1

    — internal
    declare @Year0101 date
    , @FirstMonday date

    select @Year0101=convert(date,convert(varchar,@year)+’.01.01′)
    select @FirstMonday= dateadd(DAY,case when @@datefirst = 7 then 1 else -1*(@@datefirst-1) end, case when datepart(ISO_WEEK,@Year0101)=1 then dateadd(day,-1*(datepart(WEEKDAY,@Year0101)-1),@Year0101)
    else dateadd(day,7+(-1*(datepart(WEEKDAY,@Year0101)-1)),@Year0101) end)

    — result
    select dateadd(week,@week-1,@FirstMonday)

    Reply
  • don’t know why google keep gicing me this site as a reference for SQL… Even the simplest posts are either stolen (ie, never referencing the sources that did actually found the solution) or not checked and you need to dive in the comments to find the working solution… sigh

    Reply
  • This will help you, If you are looking for start date & end date of the current week

    declare @date datetime = ‘2015-01-8’
    select datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, @date), 0)), 0), @date – 1) + 1 as ‘Current Week of this month’
    ,
    @date AS TODAY,
    LAST_MONDAY = DATEADD(wk, DATEDIFF(d, 0, @date) / 7, 0),
    [LAST_MONDAY’s SUNDAY] = DATEADD(wk, DATEDIFF(d, 0, @date) / 7, 6)

    Reply
  • i want to find 47th Thursday from a given year using sql statements

    Reply
  • Jitan H Chainani
    February 5, 2016 5:30 am

    Hi Guys,

    How would i use this formula if i need the count for the coming saturday and last saturday only? Just counts for 2 saturdays in different columns.

    for example if I am checking data on any other week day between these 2 saturdays. I should be able to know the data for last sat & the coming sat.

    Thanks in advance for your help,

    Reply
  • This does not work as intended. If you put in a Sunday date it defaults to the next Monday because inherently if you use a DateDiff function SQL it assumes Sunday is the first day of the week. Here is a note from Microsoft on their DateFirst Microsoft Doc:

    Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic.

    So you need to use something like this:

    Select case
    WHEN DATEADD(ww, DATEDIFF(ww,0,GetDate(), 0) >GetDate() THEN
    DATEADD(ww, -1, DATEADD(ww, DATEDIFF(ww,0,GetDate()), 0))
    ELSE
    DATEADD(ww, DATEDIFF(ww,0,GetDate()), 0)
    End

    Reply

Leave a Reply

Menu