A common problem users experience when working with dates is that the date() function might return an error like the following:
This error message is explaining the following:
- Where it says "Error evaluating field", it is identifying the name of a field in your form design that is having the problem (in this example, it is the field named "date_converted").
- Where it says "The problem was located in calculate expression for", the error message is telling you where to find the field in the group structure ("date_converted" is inside a form with the ID, "test_date". Groups names can also be listed in this way, after the form ID).
- Where it says "cannot convert '1980-2-30' to a date, it is telling you that SurveyCTO cannot convert the input value to a date value.
You might encounter this error when trying to validate or deploy a form design, when testing using the Test view, or when collecting data using the SurveyCTO Collect mobile app or web forms.
It is not possible to submit data with a form experiencing this error, so it is important to design and test forms that use the date() (and the datetime()) function carefully.
1. Why does this error happen?
This error is the result of improper use of the date() function, which is intolerant of invalid values. As soon as an invalid value is passed into the date() function, the above error will appear. As per the product documentation on date(), it expects a Gregorian calendar date value in the format 'YYYY-MM-DD'. Any other format, with different separators or another order of values, is not acceptable. However, date() can take values with one or two digit months and dates (e.g. '2019-1-21' and '2010-10-1' are valid date values).
2. How does this error come about?
This error may arise from passing a whole value that is invalid (e.g. '01/09/2017', which is the wrong order of values, and uses the slash "/" instead of the hyphen "-" for the separator), or when constructing a date value from parts. In the latter scenario, you might be using the concat() function to concatenate a separate year, month, and day value (as suggested here). For example:
concat(${year},'-',${month},'-',${day})
The problem is that if just one of these fields ("year", "month", and "day") has no value, then the date value is invalid by default. If all three are blank, you have effectively passed "--" (just the two dash separators) into date(), and so date() will complain.
3. How to solve for this problem
3.1 Fixing the design with whole invalid date values
If you are passing whole values into the date() function and you are having this problem, you need to look closely at the values you're passing. If the source of the whole values is pre-load data, a common problem is that the software used to generate the CSV files for pre-loading (commonly Excel) automatically formats the date values, changing how they are stored. This automatic formatting would need to be disabled.
3.2 Fixing the design with constructed date values
If you are constructing the date value (e.g. using the concat() function) and you are getting this error, the simplest solution is to have your date()'s calculate field's expression not trigger the evaluation of date() until your "year", "month", and "day" fields all have a value. The following expression would delay the evaluation of the date until it is safe to cast the constructed string value as a date:
if(${year} != null and ${month} != null and ${day} != null,
date(concat(${year}, '-', ${month}, '-', ${day})),
null)
The above expression tests whether each of the fields, "year", "month", and "day" are empty (or equal to 'null', which is a special value that means "no value"). If none are empty, the "date(concat(${year},'-',${month},'-',${day})" part of the if() logic evaluates. However, if even one field is empty, a 'null' value is stored instead. Effectively then, the evaluation of the date() function is delayed until it would no longer cause a problem.
3.3 On a mobile device
It might happen that you discover that your form design has this problem with the date() function while testing the form, or while collecting data. The data you've collected is recoverable, though. The form definition will need to be fixed as above, then updated on the server console. Once fixed, you can recover the data and continue completing the form after using the SurveyCTO Collect option to Reload with updated form. Read more here.
Unfortunately, no equivalent option exists for web forms.
Consult our documentation on using expressions in your forms to read more about the date(), concat() and if() functions.
Do you have thoughts on this support article? We'd love to hear them! Feel free to fill out this feedback form.
0 Comments