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 (http://blog.SQLAuthority.com)

About these ads

34 thoughts on “SQL SERVER – Find Monday of the Current Week

  1. This does not work. Here is an example:

    DECLARE @TODAY DATETIME
    SET @TODAY = ‘June 14, 2009′

    SELECT

    @TODAY AS TODAY,
    LAST_MONDAY = DATEADD(wk, DATEDIFF(wk,0,@TODAY), 0)

    :-)

  2. I believe that is because Sunday is classed as the first day of the week.

    Therefore when the date is a Sunday, the Monday of the current week is tomorow.

    :)

    • “I believe that is because Sunday is classed as the first day of the week.”

      If I set Monday to be the first day of the week it still doesn’t work.

      SET DATEFIRST 1;
      DECLARE @TODAY DATETIME ;
      SET @TODAY = ‘June 14, 2009′ ;
      SELECT
      @TODAY AS TODAY,
      LAST_MONDAY = DATEADD(wk, DATEDIFF(wk,0,@TODAY), 0)

      returns:

      TODAY LAST_MONDAY
      2009-06-14 00:00:00.000 2009-06-15 00:00:00.000

  3. Try this:
    DATEADD(wk, DATEDIFF(d, 0, GETDATE()) / 7, 0).

    This works because 1/1/1900 (i.e. ’0′) was a Monday so you are basically dividing the days since then by 7 days exactly.

    FYI – you can also find the day of the week based on a Monday start date (i.e. Monday = 0, Tuesday = 1, etc.) by using “% 7″ instead of “/ 7″.

  4. The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
    doesn’t return the date of last monday.

    I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.

    what if I want to run a script in any time and the result must return the date of last monday, last tuesday til last friday.

    Any way to do it?

  5. The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
    doesn’t return the date of last monday.

    I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.

    Any query can give me the date of last monday til last friday, this mean I need 5 days no matter what day I run the script.

  6. Pinal Dave – Thanks SO much – I have seen your posts before but this one gave me exactly what I wanted. and so simple… a summary for the week beginning on monday was MUCH simpler with this small bit of code! you are the BEST!

  7. I tried to find Wednesday of the current week on the same lines, but was not successful. Can you pl. help.
    For any given date, I need the Wednesday of that week.

    Thanks in advance,
    GS

  8. Hi There,

    Dave’s tit bit is perfect for defalut date caliculations. i.e. by defauls Sunday is considerded as first day of week in US.

    if you want to consider monday as first day of the week you may use the below tit bit.

    SET DATEFIRST 1
    select (DateAdd(d, – (DatePart(dw,’2011-08-21′) -1), ’2011-08-21′))

  9. Since you helped me out here, I’ll pass along what it was exactly you helped me with. This query builds a result set that lists all the weeks in the past year for a view or function:

    WITH CTE_PastYearinWeeks
    AS (
    select
    /* Thanks to SQL Authority for below. It gets the number of weeks between now and the beginning of (sql) time and adds it to the beginning of (sql) time, thus setting it to the first day of the week! */
    DATEADD(wk, DATEDIFF(wk,0,GETDATE()-365), 0) as DateStart
    UNION ALL
    SELECT DATEADD(WK,1,DateStart)
    FROM CTE_PastYearinWeeks
    WHERE DATEADD(WK,1,DateStart) <= GETDATE()
    –and DATEPART(YYYY,DateStart) <= DATEPART(YYYY,GETDATE())
    )
    select DATEPART(YYYY,DateStart) as YearNum
    , DATEPART(WK,DateStart) as WeekNum
    , SUBSTRING(CONVERT(varchar, DateStart, 107),1,6) + ' – ' + CONVERT(varchar, DATEADD(DD,6,DateStart), 107) as WeekTitle
    from CTE_PastYearinWeeks
    OPTION (MAXRECURSION 0);

  10. 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

    • 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

  11. 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)

  12. 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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s