Hi, I am writing a stored procedure to get RunDateFrom and RunDateTo based on ReportType so I can use it in other data stored procs. I need help on calculating 3 and 6 months prior dates from current date. Your help will be greatly appreciated...
My stored proc so far:
SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
ALTER proc [dbo].[get_date_range] @report_type char(5) = 'W' output, @run_date_from datetime = '1/1/1900' output, @run_date_to datetime = '1/1/1900' output as begin
/*
* The @report_type can be:
* D - daily
* W - weekly
* M - monthly
* MTD - month to date
* Y - yearly
* YTD - year to date
* YDAY - yesterday
* 3MTD - 3 months prior from current date
*
* If the from and to dates are supplied then they are returned
* If either are '1/1/1900' then use the report_type to set.
*/
declare @day_of_week int,
@run_date datetime
if ( @run_date_from = '1/1/1900' or @run_date_to = '1/1/1900')
begin
/*
* Default is for the week
*/
if @report_type not in ('D', 'W', 'M', 'MTD', 'Y', 'YTD', 'YDAY', '3MTD')
select @report_type = 'D'
if @run_date_from = '1/1/1900'
select @run_date = convert(char(10), getdate(), 101)
else
select @run_date = @run_date_from
if @report_type = 'D'
begin
select @run_date_from = @run_date
select @run_date_to = @run_date
end
else if @report_type = 'W'
begin
select @day_of_week = datepart(dw, @run_date)
if @day_of_week = 1 /* Sunday */
begin
select @run_date_from = dateadd(day, -6, @run_date)
select @run_date_to = dateadd(day, -2, @run_date)
end
else if @day_of_week = 7 /* Saturday */
begin
select @run_date_from = dateadd(day, -5, @run_date)
select @run_date_to = dateadd(day, -1, @run_date)
end
else /* Mon-Fri */
begin
select @run_date_from = dateadd(day, 2 - @day_of_week, @run_date)
select @run_date_to = dateadd(day, 6 - @day_of_week, @run_date)
end
end
else if @report_type = 'M'
begin
select @run_date_from = convert(char(2), datepart(month, @run_date))
+ '/01/' +
convert(char(4), datepart(year, @run_date))
select @run_date_to = dateadd(day, -1, (dateadd(month, 1, @run_date_from)))
end
else if @report_type = 'MTD'
begin
select @run_date_from = convert(char(2), datepart(month, @run_date))
+ '/01/' +
convert(char(4), datepart(year, @run_date))
select @run_date_to = @run_date
end
**/*else if @report_type = '3MTD'
begin
select @run_date_from = convert(char(2), dateadd(month, -3, @run_date))
+ '/01/' +
convert(char(4), datepart(year, @run_date))
select @run_date_to = @run_date
end*/**
else if @report_type = 'Y'
begin
select @run_date_from = '01/01/' +
convert(char(4), datepart(year, @run_date))
select @run_date_to = '12/31/' +
convert(char(4), datepart(year, @run_date))
end
else if @report_type = 'YTD'
begin
select @run_date_from = '01/01/' +
convert(char(4), datepart(year, @run_date))
select @run_date_to = @run_date
end
else if @report_type = 'YDAY'
begin
select @run_date_from = dateadd(day, -1, @run_date)
select @run_date_to = dateadd(day, -1, @run_date)
end
end
else
select @run_date = @run_date_to
return 0
end
