Problem with is_date

mattr

• February 24, 2011 00:00

• Answered

Follow

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.

• 

  avackova

  • February 24, 2011 10:25

  0

  Comment actions Permalink

  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
  }
  

Please sign in to leave a comment.