Kbase P65076: How to convert integer (seconds since midnight) to time in SQL-92?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
GOAL:
How to convert integer (seconds since midnight) to time in SQL-92?
FIX:
Some 4GL applications store time as an integer, normally as seconds since previous midnight. In SQL-92 you can use mathematical functions to calculate time out of such an integer value as follows:
"select ((((column - MOD(column,60)) / 60) - (MOD(((column - MOD(column,60)) / 60), 60))) / 60) as Hours,MOD(((column - MOD(column,60)) / 60), 60) as Minutes, MOD(column,60) as Seconds from table"
If you want to modify the output further, you have to convert values to character data type, e.g. CAST(expression as CHAR) and then use string formatting functions. For example, you could use LPAD function to add leading zeros and CONCAT to add expressions together. Please refer to SQL-92 Guide and Reference for more information.