|
Execute the following
Microsoft SQL Server T-SQL script in SSMS Query Editor to demonstrate the creation and test of a stored procedure to calculate last week, last month, last quarter and last year date limits using datetime variable types.
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 |
Related article:
http://www.sqlusa.com/bestpractices/datetimeconversion/
|
