Re: DATETIME --> INTEGER

In article <Cuynpq.KCq@freenet.buffalo.edu>, aq069@freenet.buffalo.edu (Kenneth S. Norton) says: > > >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? > >Thank you, >Ken Norton >-- >KENNETH S. NORTON +================================+ >Upgrade Corp of America | My opinions are barely my own, | >74437.1160@compuserve.com | and hardly my employer's. | >aq069@freenet.buffalo.edu +================================+ Had a similar problem with timesheet system a few months back, here is some ACE code to convert a interval to something else [could be integer]. Might be of use ot you: Note I used a interval datatype, in the worst case all you need to do is convert the datetime to a interval [Informix does it for you, see the manual for details on how to do it], then use the code sample below, ACe (&4gl too I bet) has a function to convert a datetime to a interval [esp. with date arithmetic, or when subtractin 2 datetime variables] While its for a ACE report, 4gl is similar enough that it might work, and if you have IFX 5 or higher, you can make this a stored procedure and call it yourself. If you aren't using 4gl or ACE, then maybe a C code function would do it for you [easy to write]. If you want one email me back. { Some of the vars I used and their types!! [not all used here in this example - too short of time to make it nice] } define variable total_time interval hour(3) to minute variable total_hours interval hour(4) to minute variable total_hours_cutoff interval hour(4) to minute variable total_minutes interval minute(6) to minute variable total_min_cutoff interval minute(6) to minute variable asc_minutes char(15) variable total_mins integer variable total_hmins numeric(9,2) end { Then in the code I had something like this: } let total_time = group total of activity_time {Activity_time is defined as a interval type as well} { I think this next line creates a cutoff at 100 hours per week, can't remember exactly now been so long since I wrote this code!, this stops wonky or erroneous intervals getting through! } let total_hours_cutoff = interval(100:0) hour(4) to minute { Convert the total_time [hours/minutes/seconds etc] to a integer number of minutes: { let total_minutes = total_time {Converts to interval of minutes} let asc_minutes = total_minutes {makes it a ASCII number} let total_mins = asc_minutes {converts it to a integer} { total_mins is now the integer number of minutes! from total_time I dont think you can dispense with the conversion to ASCII as I tried everything before I did that step which solved the puzzle! } Best of luck. Regards: Greg Nikoloff