procedure to calculate last week, last month, last quarter and last year date limits

use AdventureWorks2008;

go

create proc sprocLastPeriodRange

      @Period varchar(8),

      @PeriodStart datetime output,

      @PeriodEnd datetime output,

      @PeriodEndPlusOneDay datetime output

as

begin

declare @FirstDayOfCurrentPeriod datetime = getdate(),

        @FirstDayOfLastPeriod datetime = getdate() 

if @Period in ('Week','wk','ww')

begin

  set @FirstDayOfCurrentPeriod = DATEADD(DD, 1 - DATEPART(DW, CONVERT(VARCHAR(10),

                                  getdate(), 111)), CONVERT(VARCHAR(10), getdate(), 111))
                    set @FirstDayOfLastPeriod = dateadd(dd, -7, @FirstDayOfCurrentPeriod)
              end

else if @Period in ('Month', 'mm', 'm')

begin

      set @FirstDayOfCurrentPeriod = convert(datetime,

                               left(convert(varchar, getdate(), 111),8) + '01')

      set @FirstDayOfLastPeriod = dateadd(mm, -1, @FirstDayOfCurrentPeriod)

end

else if @Period in ('Quarter','qq', 'q')

begin

      set @FirstDayOfCurrentPeriod = CONVERT(CHAR(4), YEAR(getdate())) +

                       CASE WHEN MONTH(getdate()) between 1 and 3 THEN '/01/01'

                            WHEN MONTH(getdate()) between 4 and 6 THEN '/04/01'

                            WHEN MONTH(getdate()) between 7 and 9 THEN '/07/01'

                            ELSE '/10/01'

                       END

      set @FirstDayOfLastPeriod = dateadd(mm, -3, @FirstDayOfCurrentPeriod)

end

else if @Period in ('Year', 'yyyy', 'yy')

begin

      set @FirstDayOfCurrentPeriod = convert(datetime,

                                    left(convert(varchar, getdate(), 111),5) + '01/01')
                    set @FirstDayOfLastPeriod = dateadd(yy, -1, @FirstDayOfCurrentPeriod)
              end

set @PeriodStart = @FirstDayOfLastPeriod

set @PeriodEndPlusOneDay = @FirstDayOfCurrentPeriod

set @PeriodEnd = DATEADD (dd,-1, @FirstDayOfCurrentPeriod)

end

              go 

-- Test

declare @LastPeriodBegin datetime, @LastPeriodEnd datetime

declare @ThisPeriodBegin datetime 

exec sprocLastPeriodRange 'qq',

                          @LastPeriodBegin output,

                          @LastPeriodEnd output,

                          @ThisPeriodBegin output

select Period='qq',

       LastBegin=@LastPeriodBegin,

       LastEnd=@LastPeriodEnd,

              ThisBegin=@ThisPeriodBegin     

-- To use it for WHERE range filtering

-- Note that < is used at period upper limit

select * from Purchasing.PurchaseOrderHeader

where OrderDate >= @LastPeriodBegin

and   OrderDate <  @ThisPeriodBegin

go

             

Partial results:

Period	LastBegin	LastEnd	ThisBegin
qq	7/1/08 0:00	9/30/08 0:00	10/1/08 0:00