Find previous Saturday and Sunday

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

  • IMO a calendar table is pretty much essential

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html



    Clear Sky SQL
    My Blog[/url]

  • Thanks. I presume it can be done without a calendar table, but on looking at the link provided I can see the advantages of it as I have to do this sort of thing in a number of places.

    These 30 minute time slots are important (in some cases, not this one) and need to be identified as, for example, peak and off-peak and depend on the customer.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply