Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
functions
- Thread starter sri1961
- Start date
Sri:
You can create stored procedures and call them from SQL:
Here's a sample stored procedure:
-- This procedure returns the number of seconds between datetime dt1 and dt2.
-- change to character first because the interval to integer conversion
-- fails. Ed Schaefer 09/23/98
CREATE PROCEDURE int_in_sec(dt1 DATETIME YEAR TO SECOND, dt2 DATETIME YEAR
TO SECOND) RETURNING INTEGER;
DEFINE int_sec INTERVAL SECOND(9) TO SECOND;
DEFINE xchar CHAR(20);
DEFINE xint INTEGER;
LET int_sec = dt2 - dt1; -- get the interval
LET xchar = int_sec; -- change to char
LET xint = xchar; -- change to integer
RETURN xint;
END PROCEDURE;
Here's a sample table:
create table sample_table
(
dt1 datetime year to second,
dt2 datetime year to second
);
with sample data:
insert into sample_table values ("2002-02-28 16:00:23", "2002-02-28 17:00:24"
and with the sample select:
select int_in_sec(dt1, dt2) from sample_table
returns the value: 3601
Regards,
Ed
Schaefer
You can create stored procedures and call them from SQL:
Here's a sample stored procedure:
-- This procedure returns the number of seconds between datetime dt1 and dt2.
-- change to character first because the interval to integer conversion
-- fails. Ed Schaefer 09/23/98
CREATE PROCEDURE int_in_sec(dt1 DATETIME YEAR TO SECOND, dt2 DATETIME YEAR
TO SECOND) RETURNING INTEGER;
DEFINE int_sec INTERVAL SECOND(9) TO SECOND;
DEFINE xchar CHAR(20);
DEFINE xint INTEGER;
LET int_sec = dt2 - dt1; -- get the interval
LET xchar = int_sec; -- change to char
LET xint = xchar; -- change to integer
RETURN xint;
END PROCEDURE;
Here's a sample table:
create table sample_table
(
dt1 datetime year to second,
dt2 datetime year to second
);
with sample data:
insert into sample_table values ("2002-02-28 16:00:23", "2002-02-28 17:00:24"
and with the sample select:
select int_in_sec(dt1, dt2) from sample_table
returns the value: 3601
Regards,
Ed
Schaefer
- Status