How to get the hour of a DAY TO MINUTE interval?
Posted in 2019
Dear colleagues: I have to get the total hours worked by "N" employees for a year. I have the entry and exit records of each of them, which I obtain from an electronic clock. The date format is a DATETIME YEAR TO FRACTION (3). When I subtract the entry from the exit, I get an INTERVAL of this type: "0 00: 00: 00.000" where the first data is for the day, and the rest for hours, minutes, seconds and milliseconds. The problem arises when I try to use the hours to add them and go totaling. In a normal query, it is easy to get the hours and minutes, for example: "SELECT TO_CHAR (EXTEND (FechaFichada, HOUR to HOUR), '% H') FROM xxFichadas WHERE LegLegajo = 97 and IdFichada IN (2502323, 2502876);" This query returns me the time of dialing, but if I want to use it in a Stored Procedure, like this: "LET vHoras = TO_CHAR (EXTEND (vHorasTrabajadas, HOUR TO HOUR), '% H');" It throws me an error: "SQL Error (-1260): It is not possible to convert between the specified types." I clarify that the variable "vHorasTrabajadas" is declared as: "INTERVAL HOUR TO MINUTE;" From what I have seen, the EXTEND function does not work for small intervals (if they are part of a complete DATETIME, yes). My Informix is ​​an IDS 7.31 TD6, so the CAST function is not supported. Do any of you know any way to isolate hours and minutes separately to accumulate them and then show them? Grateful