473,883 Members | 1,594 Online

# 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;

Jul 23 '05 #1
1 6488

"Matt" <ma***********@ manning-napier.com> wrote in message
news:11******** *************@g 49g2000cwa.goog legroups.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_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;

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
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.