473,883 Members | 1,594 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.

Similar topics

0
1793
by: Steve Weet | last post by:
Does anyone know how to retrieve the values returned from an Informix Stored procedure in PHP. I have a procedure that returns 4 values but only 1 row. If I prepare it and then execute it I can not retrieve the results as all of the PHP functions seem to return an array based on the column names, wheras there is no column name associated with the return values. Alternatively is there any way to name the return values from a stored...
19
7306
by: Lauren Quantrell | last post by:
I have a stored procedure using Convert where the exact same Convert string works in the SELECT portion of the procedure but fails in the WHERE portion. The entire SP is listed below. Specifically, I have a problem with this portion in the WHERE clause: DATEADD(Day,tblMyEventTableName.ReminderDays, @DateNow) Between CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/' + str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/'...
4
9280
by: Rodusa | last post by:
I am having problem to apply updates into this function below. I tried using cursor for updates, etc. but no success. Sql server keeps telling me that I cannot execute insert or update from inside a function and it gives me an option that I could write an extended stored procedure, but I don't have a clue of how to do it. To quickly fix the problem the only solution left in my case is to convert this recursive function into one recursive...
2
4098
by: Thomas Bean | last post by:
Hello, I'm fairly new to DB2. I am having some difficulty converting some stored procedres from Informix that use default input parameters. Specifically, the Informix procedures I need to convert use the '*' character as a default input parameter to be used with the 'MATCHES' condition if the input value is null. For example: CREATE PROCEDURE "informix".stored_proc(input1 DECIMAL(18,8), input2 CHAR(20) DEFAULT '*')
5
12427
by: Gustavo Randich | last post by:
Hello, I'm writing an automatic SQL parser and translator from Informix to DB2. Now I'm faced with one of the most difficult things to translate, the "foreach execute procedure" functionality provided by Informix. This is the problem: the execution of the translated SQL leaves the rows in the temp table correctly but raises error SQL0480N. It's very simple to try it: ------------
5
18736
by: simon | last post by:
I have datetime variable: Datetime tsEndTime; Should I use (DateTime): tsEndTime=(DateTime)rdr.GetValue(15) or is better to use: tsEndTime=Convert.ToDateTime(rdr.GetValue(15))
1
8583
by: fatmapatlar | last post by:
I have to convert this script, but i didn't. This is stored procedure of sql. I need to convert to postgresql... if it is possible please help me! Thanks Fatma CREATE PROCEDURE usp_generatetable(@prm_tableID int, @prm_langID int) as
1
5387
by: teddysnips | last post by:
My clients have asked me to write a service desk application using CRM 4.0. The initial spec. looks promising, and I can't see any technical reason why this can't go ahead. Except. The core customer data (company data, contact data, contract data etc.) will not be stored in CRM. Instead, it will be stored in an Informix database. This is because the service desk will be attached to an existing real-time system which cannot be...
4
3726
by: Tim Kelley | last post by:
Is it possible to store a sql2000 timestamp field to a variable in C#. I am creating a dataset from a stored procedure and one of the fields is a timestamp field. I have created a variable of type binary, but I am not sure how to convert to data in the dataset. Thanks.
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10730
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10407
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9563
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4605
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.