Microsoft SQL Server forum - Convert Informix Stored Procedure

Author

Convert Informix Stored Procedure

Matt

2005-06-15, 1:24 pm

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_calendar_q
uarter;

CREATE FUNCTION mnaf_calc_calendar_q
uarter(pEndDate Date)
-- ********************
********************
********************
*****************
-- Name: mnaf_calc_calendar_q
uarter
-- 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(mnaf_bo
month(dtEndDate) - 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(mnaf_bo
month(dtEndDate) - 2 units
month);

END IF;

END IF;

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

RETURN dtStartDate, dtEndDate;

END FUNCTION;

grant execute on mnaf_calc_calendar_q
uarter to public;

Simon Hayes

2005-06-15, 8:24 pm

"Matt" < matt_marshall@mannin
g-napier.com> wrote in message
news:1118858793.011839.15120@g49g2000cwa.googlegroups.com...
>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_calendar_q
uarter;
>
> CREATE FUNCTION mnaf_calc_calendar_q
uarter(pEndDate Date)
> -- ********************
********************
********************
*****************
> -- Name: mnaf_calc_calendar_q
uarter
> -- 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(mnaf_bo
month(dtEndDate) - 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(mnaf_bo
month(dtEndDate) - 2 units
> month);
>
> END IF;
>
> END IF;
>
> -- Calcuate the start date by subtracting off two months
> LET dtStartDate = (mnaf_bomonth(dtEndD
ate) - 2 units month);
>
> RETURN dtStartDate, dtEndDate;
>
> END FUNCTION;
>
> grant execute on mnaf_calc_calendar_q
uarter to public;
>

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

Sponsored Links