Pad a Numeric Field with 0 in DB2

Say you’re storing a time in a 6 position numeric field in DB2 (in HHMMSS format), and you need to pull the hour. SUBSTR(FIELD,1,2) will work great as long as the time is 10am or later, but before that, you’ll start pulling weird times like 91:00. This is because SUBSTR does not pad a numeric field with zeroes in query. To force a prepended 0, do SUBSTR(DIGITS(FIELD),1,2).

One response to “Pad a Numeric Field with 0 in DB2

  1. Thanks!! This is exactly what I was trying to do and is the only way I could get time padding working in V5R2