Echoic Echoic - 7 months ago 38
SQL Question

Add leading zeros on an sql insert statement

I have a set of fields(all char) I am writing to a table(new_table) from another table(old_table). There are four fields in old_table - month, day, century, year(again, in char). I am adding these dates into new_table as one field - invdat(char). The client would like to add leading zeros to the date because they are not currently stored in old_table.

Here is a sample:

INSERT INTO new_table
SELECT month || oedy01 || oecc01 || oeyr01 || as invdat
FROM old_table

So, I would need to add a leading zero to these fields before putting them into the invdat field in the new_table.

Any advice is appreciated.


There are many reasons why a system with older software is storing dates as a character string. Quite likely, it's because the software was written before date data types were easily available in RPG. A comment above mentions it's been done this way for 20 years. There is probably a LOT of code around it and changing it to satisfy "newer" conventions is cost prohibitive. Even though DB2/400 and RPG IV handle dates quite nicely today, who wants to re-code and re-test a big system that's been working just fine for decades when there is other work to be done? That said, here is a simple way, using AS/400-happy DB2 SQL, to zero-pad your fields into a single date field:

INSERT INTO new_table 
SELECT Right('00'||month,2) || Right('00'||oedy01,2) || Right('00'||oecc01,2) || Right('00'||oeyr01,2) as invdat 
FROM old_table

I suggest verifying that date format with the AS/400 developers. Normally, you'll see a date stored that way in ccyymmdd format, but there's no hard and fast rule about that.