Derived Column to convert string to DB Date.

I have a date field in string format "0MMDDYY" and some value has "0000000".
I need to convert the string  to normal db date and also replace "0000000" with NULL.

I'm using a derived column, and assuming that the column name is [Column].

[Column] == "0000000" ? "NULL" : (DT_WSTR,2)20 + (DT_WSTR,2)RIGHT([Column],2) + "-" + (DT_WSTR,2)SUBSTRING([Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING([Column],4,2)

and if it was an INT field....

[Column] == (DT_WSTR,7)0000000 ? "NULL" : (DT_WSTR,2)20 + (DT_WSTR,2)RIGHT([Column],2) + "-" + (DT_WSTR,2)SUBSTRING([Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING([Column],4,2)

That's assuming that your dates are in the year 2000 and above...

What if the String has a date such "0110540"

The above code will give you 2040-11-05

so we'll have to check and fix the year to 1940

@[User::Column] == "0000000" ? "NULL"  :    (   (DT_I4)(DT_WSTR,2) RIGHT(@[User::Column],2)  <= 11  ? ((DT_WSTR,2)20 + (DT_WSTR,2)RIGHT(@[User::Column],2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],4,2)   )  :    ( (DT_WSTR,2)19 + (DT_WSTR,2)RIGHT(@[User::Column],2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],4,2)  ))

That's the only way since we have only 2 digits for the year, plus i'm calculating patient's age, so I don't think any of them over 100 years old !

I'm open to discussion if you have a better way of doing it..

Hope that helps..