none
How to get date - last Monday from given date? RRS feed

  • Question

  • How can I get a date that would be last/previous Monday from given date?

    For example for given 22/01/2015 it would give 19/01/2015.

    Monday, June 15, 2015 4:27 PM

Answers

  • All proposed solutions are good except for one case: it will not work if the day is Sunday.

    And if the day itself is Monday, it will return the same day which is not  in my opinion the last Monday.

    For that, here it is my solution (inspired from VS-SQL proposition):

    DECLARE @PostDate DATE = '20150614'
    
    IF DATEPART(DW,@PostDate ) < 3 
    	SELECT DATEADD(DD,-5- DATEPART(DW,@PostDate ),@PostDate) "PostDate"
     ELSE
    	SELECT DATEADD(DD,2- DATEPART(DW,@PostDate ),@PostDate) "PostDate"

    Hope it helps!

    • Marked as answer by phinix-mike Wednesday, June 17, 2015 9:22 AM
    Monday, June 15, 2015 8:32 PM
  • One more method

    declare @d datetime
    set @d = '20150122'


    declare @baseMonday datetime
    set @baseMonday = '17530101'

    select
      @d,
      @baseMonday + datediff(day,@baseMonday,@d)/7*7 as Monday


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 16, 2015 3:38 AM
    Answerer

All replies

  •  declare @dt date='2015-1-22'
     
     select dateadd(week,datediff(week,0,@dt),0)

    • Proposed as answer by VS_SQL Monday, June 15, 2015 7:16 PM
    Monday, June 15, 2015 4:37 PM
    Moderator
  • Using a calendar table, it's easy:

    SELECT c.today, c2.today AS lastMonday
      FROM calendar c
        INNER JOIN calendar c2
    	  ON c2.today = (SELECT MAX(today) FROM calendar WHERE weekDay = 2 AND today < '2015-06-15')
          AND c.today = '2015-06-15'


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Proposed as answer by disssss Monday, June 15, 2015 8:47 PM
    Monday, June 15, 2015 6:02 PM
  • SET DATEFIRST 7
    DECLARE @PostDate DATE = '20150122'
    SELECT DATEADD(DD,2-DATEPART(DW,@PostDate ),@PostDate) "PostDate"

    Try the above logic it should work. It always pulls the previous monday for the given date.

    "SET DATEFIRST 7" will set the first day of the week as Sunday..

    reference - https://msdn.microsoft.com/en-us/library/ms174420.aspx


    VSP

    • Proposed as answer by VS_SQL Monday, June 15, 2015 7:16 PM
    • Unproposed as answer by Jingyang LiModerator Tuesday, June 16, 2015 1:08 PM
    Monday, June 15, 2015 7:11 PM
  • Changing server settings is not a good solution. There could be any number of other database objects which rely on the settings being what they are.

    Also, VS_SQL, forum etiquette prevents us from proposing our own solutions as answers. While you may believe (and it might well be true) that your solution is the best one, it's best to leave the proposal to other users. Obviously you think it's a good answer, otherwise you wouldn't have posted it!


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Monday, June 15, 2015 7:27 PM
  • Sorry, Patrick if my post is not clear enough...

    we don't have to change any server settings, by default Sunday is firstday of week.

    I am just posting it for info so that he can refer to that.

    fyi.. I have proposed Jingyang post as answer.

    Your soln, only works if there exists a calendar table.


    VSP

    Monday, June 15, 2015 7:42 PM
  • hey Phinix, if you supply Sunday as post date, by default it will pull the next Monday. if the requirement is to pull previous Monday then try the below code.

    DECLARE @PostDate DATE = '20150614'
    SELECT DATEADD(DD,2-CASE WHEN DATEPART(DW,@PostDate )=1 THEN 8 ELSE DATEPART(DW,@PostDate) END,@PostDate) "PostDate"

    Thanks,


    VSP

    Monday, June 15, 2015 7:56 PM
  • By DEFAULT it is.

    If that's not the case on the OPs server changing it could mangle other objects.

    Yeah, you did. You also proposed your own.

    Yes, my solution requires a calendar table, but it is also links to an article describing how to create one :).


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Monday, June 15, 2015 8:00 PM
  • All proposed solutions are good except for one case: it will not work if the day is Sunday.

    And if the day itself is Monday, it will return the same day which is not  in my opinion the last Monday.

    For that, here it is my solution (inspired from VS-SQL proposition):

    DECLARE @PostDate DATE = '20150614'
    
    IF DATEPART(DW,@PostDate ) < 3 
    	SELECT DATEADD(DD,-5- DATEPART(DW,@PostDate ),@PostDate) "PostDate"
     ELSE
    	SELECT DATEADD(DD,2- DATEPART(DW,@PostDate ),@PostDate) "PostDate"

    Hope it helps!

    • Marked as answer by phinix-mike Wednesday, June 17, 2015 9:22 AM
    Monday, June 15, 2015 8:32 PM
  • The Calendar table solution will work fine
    Monday, June 15, 2015 8:49 PM
  • One more method

    declare @d datetime
    set @d = '20150122'


    declare @baseMonday datetime
    set @baseMonday = '17530101'

    select
      @d,
      @baseMonday + datediff(day,@baseMonday,@d)/7*7 as Monday


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 16, 2015 3:38 AM
    Answerer
  • Another similar method is below which will also work for date and datetime2 type variables

    declare @d date
    set @d = '20150122'
    
    
    select
      @d,
       dateadd(day,(datediff(day,0,@d)/7)*7,0) as Monday


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, June 16, 2015 5:37 AM
  • I think this might work for you without any changes.

    In case @@datefirst != 7 just calculate the "6 +" based on the value you get.

    Select @@DATEFIRST -- 7 means sunday -- Just for checking
    Declare @CurrentDateTime datetime = '2015-06-01 13:48:45.200' --getdate()
    
    Select 
    	-- days back to last Sunday and go back 6 days
    	dateadd(dd, - (6 + datepart(day, @CurrentDateTime) % @@DATEFIRST), @CurrentDateTime )

    Assaf


    אסף שלם

    Tuesday, June 16, 2015 12:51 PM
  • All proposed solutions are good except for one case: it will not work if the day is Sunday.

    And if the day itself is Monday, it will return the same day which is not  in my opinion the last Monday.

    For that, here it is my solution (inspired from VS-SQL proposition):

    DECLARE @PostDate DATE = '20150614'
    
    IF DATEPART(DW,@PostDate ) < 3 
    	SELECT DATEADD(DD,-5- DATEPART(DW,@PostDate ),@PostDate) "PostDate"
     ELSE
    	SELECT DATEADD(DD,2- DATEPART(DW,@PostDate ),@PostDate) "PostDate"

    Hope it helps!

    Yep, this fixes the problem of hitting Monday or Sunday.

    Calendar table also works, now I'm thinking of using it for other purposes in my database.

    Guys, thank you all for your answers, I was shocked this morning seeing so many options, thank you very much:)

    Wednesday, June 17, 2015 9:24 AM
  • declare @dt date='2015-06-14'
     
     select dateadd(week,datediff(week,0,@dt) 
     - (Case when dateadd(week,datediff(week,0,@dt),0)>= @dt Then 1 Else 0 END)
     ,0)

    Wednesday, June 17, 2015 1:47 PM
    Moderator
  • You can use a reference date that happend to be a Monday like '19000101'. Now calculate the difference in days between the two dates, apply integer division by 7 (will truncate the last days after the last Monday), multiply by 7 and add those days to the reference date.

    DECLARE @PostDate DATE = '20150614'
    
    SELECT
        @PostDate,
        DATEADD(DAY, (DATEDIFF(DAY, '19000101', @PostDate) / 7) * 7, '19000101') AS PrvMonday_Inclusive;
    
    

    This will give you the last Monday inclusive, meaning if the date is a Monday then it will return same date. You can substract one day if you want it to be exclusive.

    Same approach could be used for any day of the week. Just find a reference date that happend to be that day of the week.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    Wednesday, June 17, 2015 2:20 PM
    Moderator
  • Clay L gave a good answer that works on stackoverflow forum

    i.e. this example:

    SELECT DATEADD(d, -((DATEPART(WEEKDAY, '20150122') - DATEPART(dw, '19000101') + 7) % 7), '20150122')

    "DATEPART(dw, '19000101')" will determine your "datefirst" setting since 1900-01-01 was on a Monday.

    If you want a Tuesday , you can change 19000101 to 19000102 etc ..

    Wednesday, February 20, 2019 12:31 AM
  • If we give current date as 'Monday' eg. 20190225, it fails to show us previous/last monday and instead of that it shows the same monday.

    Thanks,

    Arul

    Sunday, February 24, 2019 11:11 AM
  • If we give current date as 'Monday' eg. 20190225, it fails to show us previous/last monday and instead of that it shows the same monday.

    Thanks,

    Arul

    just make it like this and it will work for Monday as well

    select
      @d,
       dateadd(day,(datediff(day,1,@d)/7)*7,0) as Monday


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Sunday, February 24, 2019 11:42 AM
    Sunday, February 24, 2019 11:42 AM