locked
Find previous Saturday and Sunday RRS feed

  • Question

  • Sample here populates a table for all of August 2011, incremented in 30 minute intervals:
    declare @IntervalTable table
    (
            Interval smalldatetime primary key
    )
    
    declare @FirstInterval smalldatetime
    declare @LastInterval smalldatetime
    declare @ThisInterval smalldatetime
    
    select @FirstInterval = '08/01/2011 00:00'
    select @LastInterval = '08/31/2011 23:30'
    
    select @ThisInterval = @FirstInterval 
    while @ThisInterval <= @LastInterval 
    begin
            insert  @IntervalTable(Interval)
                    values(@ThisInterval)
            
            select @ThisInterval = DATEADD(MINUTE, 30, @ThisInterval)
    end
    
    

    I want to find the previous Saturday:
    --find Saturday of previous weekend
    declare @Today smalldatetime
    
    select @Today = '08/31/2011' --(Wed 31)
    --return  '08/27/2011 00:00' (Sat 27)
    
    select @Today = '08/28/2011' --Sun 28
    --return  '08/27/2011 00:00' (Sat 27)
    
    select @Today = '08/27/2011' --Sat 27
    --return  '08/20/2011 00:00' (Sat 20)
    
    


    And Sunday:
    --find Sunday of previous weekend
    declare @Today smalldatetime
    
    select @Today = '08/31/2011' --(Wed 31)
    --return  '08/28/2011 00:00' (Sun 28)
    
    select @Today = '08/28/2011' --Sun 28
    --return  '08/28/2011 00:00' (Sun 28)
    
    select @Today = '08/27/2011' --Sat 27
    --return  '08/21/2011 00:00' (Sun 21)
    
    

    If today is a Sunday, need to find the previous Saturday (i.e. the day before) and this Sunday (i.e. today), not the previous one. Basically, I'm looking for the previous whole weekend, or this weekend if it's a Sunday - if this makes sense...
    I'm on SQL Server 2005.
    Sunday, September 4, 2011 6:57 PM

Answers

  • SELECT DATEADD(DAY, DATEDIFF(DAY, '18991231', @anydate) / 7 * 7, '18991231')

     

    will give you last sunday passed. If today is sunday, it will give you today.

     


    N 56°04'39.26"
    E 12°55'05.63"
    • Marked as answer by HunchbackMVP Sunday, September 4, 2011 8:20 PM
    Sunday, September 4, 2011 7:18 PM
  • Thanks to both of you!
    • Marked as answer by kuopaz Sunday, September 4, 2011 8:51 PM
    Sunday, September 4, 2011 8:51 PM

All replies

  • Thanks! Assume this does not depend on the DATEFIRST setting. Should be simpler, how do I find the previous Saturday - always the previous one, even if Saturday - again not based on the week starting day setting?
    Sunday, September 4, 2011 7:45 PM
  • The idea used by Peso will suit well your needs. He is using an anchor date that happend to be the date of the week you are looking for. If you use an anchor date that happend to be a Saturday, then using this formula will give you always the previous Satuday to specific date.

    declare @anydate date = '20110827';

    SELECT DATEADD(DAY, (DATEDIFF(DAY, '19000106', @anydate) - 1) / 7 * 7, '19000106') prv_sat;
    GO

     


    AMB

    Some guidelines for posting questions...

    Sunday, September 4, 2011 8:27 PM
  • Thanks to both of you!
    • Marked as answer by kuopaz Sunday, September 4, 2011 8:51 PM
    Sunday, September 4, 2011 8:51 PM