Casting partial dates in Postgresql

Question

I have a dataset that contains a birthdate attribute. The date format is M/D/YYYY(single digit representation such as 5/8/2009) and stored in text format. Now, I need to change the column type to date. But, there are many entities (more than that can be fixed manually) that have partial dates - that is, either day or both day and month are missing. For example:

"//1935"
"/5/1929"
"/4/1931"

Now, when I apply to_date(birthdate,'DD/MM/YYYY') I get error because Postgresql cannot parse these dates. What I need to do, is to fill in the missing values with a default value. For instance, "//1935" and "/5/1929" should be converted to "1/1/1935" and "1/5/1929" respectively. Is there a quick way of fixing this problem? Or, do I need to write a regular expression parser?

Answer 1

SQL Fiddle Demo

CREATE TABLE product
    ("inputDate" varchar(9))
;

INSERT INTO product
    ("inputDate")
VALUES
    ('//1935'),
    ('/5/1929'),
    ('/4/1931')
;

SELECT *,
       CASE WHEN substr("inputDate", 1, 2) = '//' THEN '1/1/' || substr("inputDate", 3, 10)
            WHEN substr("inputDate", 1, 1) = '/' THEN '1/' || substr("inputDate", 2, 10)
            ELSE "inputDate"
       END as result
FROM Product;