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_quarter;
CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
--************************************************** ***************************
-- Name: mnaf_calc_calendar_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:
-- PgmrDate # Description
-- HPI05/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_bomonth(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_bomonth(dtEndDate) - 2 units
month);
END IF;
END IF;
-- Calcuate the start date by subtracting off two months
LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);
RETURN dtStartDate, dtEndDate;
END FUNCTION;
grant execute on mnaf_calc_calendar_quarter to public;"Matt" <matt_marshall@.manning-napier.com> wrote in message
news:1118858793.011839.15120@.g49g2000cwa.googlegro ups.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_quarter;
> CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
> --************************************************** ***************************
> -- Name: mnaf_calc_calendar_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(mnaf_bomonth(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_bomonth(dtEndDate) - 2 units
> month);
> END IF;
> END IF;
> -- Calcuate the start date by subtracting off two months
> LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);
> RETURN dtStartDate, dtEndDate;
> END FUNCTION;
> grant execute on mnaf_calc_calendar_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
