Need to add leading zeroes to string representing a date

Question

I have a string that represents a date in the format MM/DD/YY but some dates that I need to insert into a table are missing the leading zero on the MM and/or DD.

For example, '9/6/02'.

I need to add (when necessary) leading zeroes to the MM and DD portion of the string before inserting the value into a varchar2 column.

How to do this?

Currently, the DBA has the column as a varchar2. No calculations or comparisons are done with the column so it is just for reporting purposes. I will recommend changing the column to be a date type, however in the meantime I need a solution that will work with the varchar2 column.

Answer 1

My first recommendation would be to use a DATE column type as it is best practices.

If that isn't possible, you can convert the string into a date, then put that in the format you need:

SELECT
  TO_CHAR(TO_DATE(Column,'MM/DD/YY'),'MM/DD/YY') /* I would recommend ISO standard YYYY-MM-DD so the column will sort properly) */
FROM
  /* etc */