Casting partial dates in Postgresql
<p>I have a dataset that contains a birthdate attribute. The date format is <code>M/D/YYYY</code>(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:</p>

<pre><code>"//1935"
"/5/1929"
"/4/1931"
</code></pre>

<p>Now, when I apply <code>to_date(birthdate,'DD/MM/YYYY')</code> 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, <code>"//1935"</code> and <code>"/5/1929"</code> should be converted to <code>"1/1/1935"</code> and <code>"1/5/1929"</code> respectively. Is there a quick way of fixing this problem? Or, do I need to write a regular expression parser? </p>