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).

Back to Top