Skip to content
Advertisement

Convert varchar to date in Oracle SQL with month name

I have 2 columns in a large data set. Col 1 “review_date” is a varchar “September 16, 2019” I would like to have this information in DATE format as 09/16/2019

Col 2 “complete_date” is a varchar “20190916” I would like to have this information in DATE format as 09/16/2019

I have to apply this to both columns for the entire table. I am at a lost for col 1, because of the month being spelled out, and col 2 is not working for me using to_date(). My goal is to have this information even if new columns have to be created

Advertisement

Answer

Use

        TO_DATE(review_date, 'MONTH DD, 
        YYYY' )

       TO_DATE(complete_date, 
       'YYYYMMDD') 

This will read your data in the given month dd yyyy format and will convert to orackes default date format. You cannot actually modify the default date format using to_date() but can use to_char() to store modified date format as varchar

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement