convert varchar to date for 7/15/2012

Database

convert varchar to date for 7/15/2012

select cast('07/15/2012' as date format 'MM/DD/YYYY'); -- working fine 

select cast('7/15/2012' as date format 'MM/DD/YYYY'); -- Invalid date error

all my date are stored in 7 insted of 07, how i need to convert to date format?

1 REPLY
Enthusiast

Re: convert varchar to date for 7/15/2012

If the same is not applicable for month as well, then you can check the size of the string and depending on it concatenate it with 0.

 CASE WHEN CHAR_LENGTH(DATE_COL) <10

THEN 0 || TRIM(DATE_COL)