IBM DB2 V6R10 Suppressed Zero Time FMT to Time String

DecimalTimeToStringFmt.sql

SELECT 
    /*Tday Value as it appears in db*/
    lmtday as tday,
    '    24 HR Format hh:mm:ss     ' as format1,
    /*24 Hour Time Format*/
      /*get Hours*/
      SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),1,2) as HH24,
      /*get Minutes*/
      SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),3,2) as MM24,
      /*get Seconds*/ 
      SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),5,2) as SS24,
      /*Combine above to make 24 hour format 24hh:mm:ss*/
      concat(concat(concat(concat(
      SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),1,2),':'),/*concat hh:*/
      SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),3,2)),':'),/*concat mm:*/
      SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),5,2)) as HH24MMSS,/*concat ss*/
'    12 HR Format hh:mm:ss AM     ' as format2,
    /*12 Hour Time Format*/
      /*get Hours*/
      case when(INTEGER(SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),1,2))>12)then (Integer(SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),1,2)))-12 else (Integer(SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),1,2))) end as HH12,
      /*get Minutes*/  
      SUBSTR((RIGHT (REPEAT ('0',6) || lmtday,6)),3,2) AS MM12,
      /*get Seconds*/      
      SUBSTR((RIGHT (REPEAT ('0',6) || lmtday,6)),5,2) AS SS12,
      /*get AM or PM*/      
      case when(INTEGER(SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),1,2))<12)then 'AM' else 'PM' end as AM_PM,
      
      /*Combine Above to get 12HH:mm:ss AM*/
      concat(concat(concat(concat(concat(concat(
      case when(INTEGER(SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),1,2))>12)then (Integer(SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),1,2)))-12 else (Integer(SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),1,2))) end,':'),/*concat hh:*/
      SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),3,2)),':'),/*concat mm:*/
      SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),5,2)),' '),/*concat ss */
      (case when(INTEGER(SUBSTR ((RIGHT (REPEAT ('0',6) || lmtday,6)),1,2))<12)then 'AM' else 'PM' end)) as HH12MMSS
FROM F554108 FETCH FIRST 50 rows only