Calculations with date 'strings' and trying to add a year?

[PeterGL]

My question is how can I set up the below calculation to cast the string as a date AND add one calendar year to the effective date? For example paid_to_date <= (effective_date + 1 year)? An example of both date fields would be '20120101' or similar. I have tried searching this forum, Google, Framework Manager documentation, etc. Any help would be greatly appreciated.

IF( cast([paid_to_date] AS date) <= cast([effective_date] AS date))
THEN
(1)
ELSE
(0)

This returns the error:
RQP-DEF-0177 An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.
UDA-SQL-0144 An arithmetic exception was detected.
UDA-EE-0029 A date/time value is invalid.
UDA-SQL-0460 A general exception has occurred during local processing.
UDA-CUR-0000 Unable to fetch the row.

I have tried changing the calculation and testing in MS SQL but so far I have encountered some permutation of the error above. For example, this MS SQL works for adding the year to paid_to_date:
CONVERT(datetime, (DATEADD(year, 1, paid_to_date)),112) AS paid_to_date_plus_year

We have an older legacy mainframe system that stores date fields as strings (YYYYMMDD). The field in our MS SQL database is char(8) and it is not feasible to change the format at this time due to existing reports and interfaces, and also because the mainframe will be going away in a year or two. Also there are some rows of data returned with the date of '01'. For the purposes of this report it is not feasible to change the '01' from these records.

Any advice? Thanks!

[cognostechie]

This will add one year to the Effective Date. So if the Effective Date is 8th May,2012 it will become 8th May 2013

_add_years(
cast(
substring([effective_date],1,4) + '-' +
substring([effective_date],5,2) + '-' +
substring([effective_date],7,2)
,date)
,1)

[Lynn]

I suspect the cause of your error is values in those fields that cannot be converted to a date. Perhaps the '01' values you refer to are examples of this.

Put yourself in the shoes of the cast operation --- how do you convert '01' to a date??
What if you have 20120500 -- there is no such thing as day 00.
What if you have 20120237 -- there is no 37th day of Februrary.

I also suffer with a legacy system that stores dates in a non-date datatype (integer in my case). If you have values that cannot be converted to a date you'll need a business rule that transforms them somehow. In my case we put any value of zero to Jan 1, 1900. You can use case logic to do this. In other circumstances we look for the 00 day and change it to 01. It depends on what your business users decide is the appropriate method for handling these. I suppose null is another option.

[PeterGL]

Thanks for the help. Lynn -- you are right. If I exclude any non-date entries I get back valid results. I will work with the business users to convert these to a specific date (like 01/01/1900). Thanks for the help!