Re: DATETIME --> INTEGER
aq069@freenet.buffalo.edu (Kenneth S. Norton) writes:
>In order to track call lengths for billing purposes,
>my company needs to export a column from the call-
>tracking database to the billing database. The call-
>tracking column is in a DATETIME MINUTE to SECOND
>format while the receiving column requires an INTEGER.
>Essentially, I must find a way to turn 3:30 into 3.5.
>Everything I have tried (views, duration, etc.) hasn't
>worked. Does anybody have a suggestion short of
>changing the DATETIME format in the original table
>to an INTEGER?
You contradict yourself slightly, asking for and INTEGER of 3.5.
My example shows how to get total seconds into an INTEGER, but of
course a similar approach could get you DECIMAL minutes.
I shold also point that that there would be a simpler approach than the
one I show if 4GL were available, but it looks to me like you want a
straight SQL solution.
The trick is; you can't convert DATETIME into INTEGER directly, but you
can convert it to CHAR, and you can do math on CHAR columns that
contain only numbers.
so:
1) convert your DATETIME into two CHARS, one for minutes and one for
seconds, by using the EXTEND function.
2) update your INTEGER column to 60 * minutes + seconds. Viola'! You
have total seconds.
Below is my test sql script
=======================================================================
Dennis J. Pimple dennisp@informix.com Opinions expressed
Senior Consultant -------------------- are mine, and do not
Informix Software Inc Voice: 303-850-0210 necessarily reflect
Denver Colorado USA Fax: 303-779-4025 those of my employer.
{* Use this to create and populate a test table *}
CREATE TEMP TABLE dtm (dtm DATETIME MINUTE TO SECOND);
INSERT INTO dtm VALUES ("00:00");
INSERT INTO dtm VALUES ("00:01");
INSERT INTO dtm VALUES ("00:30");
INSERT INTO dtm VALUES ("00:45");
INSERT INTO dtm VALUES ("00:59");
INSERT INTO dtm VALUES ("24:59");
INSERT INTO dtm VALUES ("01:00");
INSERT INTO dtm VALUES ("01:01");
INSERT INTO dtm VALUES ("01:30");
INSERT INTO dtm VALUES ("01:45");
INSERT INTO dtm VALUES ("01:59");
INSERT INTO dtm VALUES ("59:59");
INSERT INTO dtm VALUES ("11:00");
INSERT INTO dtm VALUES ("11:01");
INSERT INTO dtm VALUES ("11:30");
INSERT INTO dtm VALUES ("11:45");
INSERT INTO dtm VALUES ("11:59");
INSERT INTO dtm VALUES ("24:59");
INSERT INTO dtm VALUES ("58:00");
INSERT INTO dtm VALUES ("23:01");
INSERT INTO dtm VALUES ("58:30");
INSERT INTO dtm VALUES ("23:45");
INSERT INTO dtm VALUES ("58:59");
INSERT INTO dtm VALUES ("59:59");{* put the MINUTES TO SECONDS DATETIME from the dtm table into two *}
{* seperate columns in another temp table *}
CREATE TEMP TABLE rtm
(mch CHAR(2), {* CHAR version of MINUTES *}
sch CHAR(2), {* CHAR version of SECONDS *}
secs INTEGER); {* (60 * MINUTES) + SECONDS *}{* Put the stuff from the real table into DATETIME columns of temp *}
INSERT INTO rtm (mch,sch)
SELECT EXTEND(dtm, MINUTE TO MINUTE), EXTEND(dtm, SECOND TO SECOND)
FROM dtm;{* Do the math on the CHAR VALUES to set seconds. *}
UPDATE rtm SET SECS = (60 * mch) + sch WHERE 1=1;{* rtm.secs now has seconds, which you can see by the SELECT below *}
SELECT * FROM rtm;