Convert Informix Stored Procedure

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Matt
    #1

    Convert Informix Stored Procedure

    I would like to convert a couple informix stored procedures to SQL
    Server stored procedures. I have no idea how to accomplish this. Here
    is an example of one of the procedures I need to convert.

    drop function mnaf_calc_calen dar_quarter;

    CREATE FUNCTION mnaf_calc_calen dar_quarter(pEn dDate Date)
    --*************** *************** *************** *************** *************** **
    -- Name: mnaf_calc_calen dar_quarter
    -- Description/Notes:
    -- Calculates the most recent calendar quarter based on the end date.
    --
    -- Parms:
    -- End Date.
    -- Returns:
    -- The calculated period start date and end date.
    --
    --*************** *************** *************** *************** *************** **
    -- Revisions:
    -- Pgmr Date # Description
    -- HPI 05/03/2005
    --*************** *************** *************** *************** *************** **

    RETURNING date, date;

    DEFINE dtStartDate date;
    DEFINE dtEndDate date;

    LET dtStartDate = mdy(12,31,1899) ;
    LET dtEndDate = pEndDate;

    -- If the end date parameter is equal to a calendar quarter,
    -- calculate the start date by subtracting three months.
    IF month(pEndDate) = 3 or month(pEndDate) = 6 or
    month(pEndDate) = 9 or month(pEndDate) = 12 then

    LET dtEndDate = pEndDate;

    ELSE

    -- Otherwise find the closest previous calendar quarter end date
    -- then calculate the start date.
    IF month(pEndDate) = 1 or month(pEndDate) = 4 or
    month(pEndDate) = 7 or month(pEndDate) = 10 then

    -- Subtract 1 month off end date parameter to get the calendar
    qtr end date
    LET dtEndDate = mnaf_eomonth(mn af_bomonth(dtEn dDate) - 1 units
    month);

    ELSE

    -- Month must be equal to 2, 5, 8, 11
    -- Subtract 2 months off end date parameter to get the calendar
    qtr end date
    LET dtEndDate = mnaf_eomonth(mn af_bomonth(dtEn dDate) - 2 units
    month);

    END IF;

    END IF;

    -- Calcuate the start date by subtracting off two months
    LET dtStartDate = (mnaf_bomonth(d tEndDate) - 2 units month);

    RETURN dtStartDate, dtEndDate;

    END FUNCTION;

    grant execute on mnaf_calc_calen dar_quarter to public;

    Tags: None
    • Simon Hayes
      #2
      Re: Convert Informix Stored Procedure


      "Matt" <matt_marshall@ manning-napier.com> wrote in message
      news:1118858793 .011839.15120@g 49g2000cwa.goog legroups.com...[color=blue]
      >I would like to convert a couple informix stored procedures to SQL
      > Server stored procedures. I have no idea how to accomplish this. Here
      > is an example of one of the procedures I need to convert.
      >
      > drop function mnaf_calc_calen dar_quarter;
      >
      > CREATE FUNCTION mnaf_calc_calen dar_quarter(pEn dDate Date)
      > --*************** *************** *************** *************** *************** **
      > -- Name: mnaf_calc_calen dar_quarter
      > -- Description/Notes:
      > -- Calculates the most recent calendar quarter based on the end date.
      > --
      > -- Parms:
      > -- End Date.
      > -- Returns:
      > -- The calculated period start date and end date.
      > --
      > --*************** *************** *************** *************** *************** **
      > -- Revisions:
      > -- Pgmr Date # Description
      > -- HPI 05/03/2005
      > --*************** *************** *************** *************** *************** **
      >
      > RETURNING date, date;
      >
      > DEFINE dtStartDate date;
      > DEFINE dtEndDate date;
      >
      > LET dtStartDate = mdy(12,31,1899) ;
      > LET dtEndDate = pEndDate;
      >
      > -- If the end date parameter is equal to a calendar quarter,
      > -- calculate the start date by subtracting three months.
      > IF month(pEndDate) = 3 or month(pEndDate) = 6 or
      > month(pEndDate) = 9 or month(pEndDate) = 12 then
      >
      > LET dtEndDate = pEndDate;
      >
      > ELSE
      >
      > -- Otherwise find the closest previous calendar quarter end date
      > -- then calculate the start date.
      > IF month(pEndDate) = 1 or month(pEndDate) = 4 or
      > month(pEndDate) = 7 or month(pEndDate) = 10 then
      >
      > -- Subtract 1 month off end date parameter to get the calendar
      > qtr end date
      > LET dtEndDate = mnaf_eomonth(mn af_bomonth(dtEn dDate) - 1 units
      > month);
      >
      > ELSE
      >
      > -- Month must be equal to 2, 5, 8, 11
      > -- Subtract 2 months off end date parameter to get the calendar
      > qtr end date
      > LET dtEndDate = mnaf_eomonth(mn af_bomonth(dtEn dDate) - 2 units
      > month);
      >
      > END IF;
      >
      > END IF;
      >
      > -- Calcuate the start date by subtracting off two months
      > LET dtStartDate = (mnaf_bomonth(d tEndDate) - 2 units month);
      >
      > RETURN dtStartDate, dtEndDate;
      >
      > END FUNCTION;
      >
      > grant execute on mnaf_calc_calen dar_quarter to public;
      >[/color]

      See "Date and Time Functions" in Books Online - DATEPART() and DATEADD()
      will probably be the ones you're looking for. This article might also be
      useful for general background information about manipulating datetime data:

      http://www.karaszi.com/sqlserver/info_datetime.asp


      Simon


        Comment

        Previous template Next
        Working...