1

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.

0
4

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 */
2
  • Using data type DATE for date values is not just "best practices" - storing date values as string is a design flaw. – Wernfried Domscheit Dec 18 '20 at 22:00
  • @WernfriedDomscheit I'm not in disagreement – bbaird Dec 18 '20 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.