question

icap avatar image
icap asked

How to get 3 and 6 months prior date from current date?

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

datetime
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.