Problem with is_date

CloverDX Platform
1

Hi, I am trying to create a Clover graph that will convert date fields (stored as varchar in the source SQL Server DB) and write them to a destination SQL Server DB (smalldatetime). Most are valid dd/MM/yyyy values, and I can successfully load them with input metadata for these fields defined as string and output metadata as date (yyyy-MM-dd), but I tried to set any invalid dates (eg 2010-50-10) to null using
iif(is_date($0.POLICY_START_DATE,“yyyy-MM-dd”),$0.POLICY_START_DATE,null) in a REFORMAT, but this doesn’t seem to work - the 2010-50-10 just goes through unchanged and fails at the subsequent REFORMAT which converts them to date metadata.

Interpreter runtime exception on line 8 column 45 : bad data when mapping field “$0.POLICY_START_DATE” (POLICY_START_DATE:date) - assigning “2000-50-10” (STRING)

Please can you advise what I am missing?
Thanks, Matt.

2

I’m not sure, why the invalid date is passed through, but following code works properly:

//#CTL1

// Transforms input record into output record.
function transform() {
	$0.field1 := str2date($field1,"dd/MM/yyyy");

	return ALL
}

// Called only if transform() throws an exception.
function transformOnError(errorMessage, stackTrace) {
	$0.field1 := iif(is_date($0.field1,"yyyy-MM-dd"), str2date($field1,"yyyy-MM-dd"), null);
	return ALL
}