Validation on parse to date function at Excel import

I want to import an Excel sheet including a date time column. To parse the string to date time I used a subflow at the main import flow, in which I parse the string to date/time in a create variable action (with error handling): parseDateTime($Parameter_String, 'MM/dd/yyyy'). When someone for example fills in 25/25/2011, the parse to date function adds the 13 extra months to the date 12/25/2011 instead of giving an error. Does anyone know how to put a validation on the parse to date function, to check if the date is valid?
3 answers

Apparantly the default option for parsing a date (in Java) is to be lenient, but you can set it to be more strict with setLenient(false), here is a small code example for a Java action with a more strict date parsing. It should take two parameters, the dateFormat as a string (for example: MM/dd/yyyy) and the date as a string (for example: 25/25/2011) and it should return a date.

SimpleDateFormat smf = new SimpleDateFormat(dateFormat);
return smf.parse(dateString);

You also need to add this import:

import java.text.SimpleDateFormat;

It will throw an exception when trying to parse an invalid date (such as the one in the example)


Since you're parsing a string in this subflow you could use the substring function on the month substring($Parameter_String,0,2)which you parse to integer and verify it is less than or equal to 12. It's another step but you could verify each aspect of the string before parsing DateTime.


Looks like a bug to me, this doesn't make sense.