Dates pulled from external data file can't be converted to a date

Satinder · August 23, 2022, 4:50pm

1. What is the issue?.
Hi,
I am trying to calculate the year difference from today's date and date pulled from external csv file. while checking the form on Enketo it can calculate easily. but on mobile using ODK collect app it says the value "1-apr-1990" (for say) can't be converted into a date.

2. What have you tried to fix the issue?
I have converted the date formats into Gregorian Calendar in the CSV file and also tried with many other formats but no success.
I have also checked that calculating the days difference from manually entered dates works fine on both Enketo and ODK collect android app.

Do anyone knows what is the issue and how to resolve it?

Test forms and external file is attached herewith
Book1.xlsx (14.4 KB)
demo.csv (42 Bytes)

danbjoseph · August 23, 2022, 9:06pm

I think this open issue is related to what you are trying to do.

github.com/getodk/javarosa

Validate reports bogus type mismatch error when checking XPath date arguments

opened 09:04AM - 13 Feb 19 UTC

tiritea

bug

#### Software versions javaRosa as it exists in Validate as it exists in XSL…Form Online v1.3.1 #### Problem description Validate appears to be performing a static evaluation of XPath expressions as part of its validation of XPath date/time/dateTime function arguments. But because it performs a static evaluation, it does not pickup valid runtime values, and as a consequence can throw a bogus type-mismatch error when it sees an invalid dateTime string (even though in actual runtime this string would contain a valid date string). #### Steps to reproduce the problem see https://forum.opendatakit.org/t/validate-issue-for-decimal-date-time/17710/6 specifically: >The simplest testcase I was able to come up with exhibiting the behavior is the above, namely > > ``` > date: year, default=2019 > result = decimal-date-time(concat(${year},"-01-01")) > ``` >which is valid and runs fine in Collect, but causes a form type-mismatch error with Validate. I suspect this may only apply to date/time functions type-checking, but until we can isolate it in the code I cant be 100% certain this wont pop up in other non-date related cases. #### Expected behavior Validate should either not attempt to (statically) evaluate XPath expression in order to guess their type, for the purpose of type checking function arguments, or must *fully* evaluate all XPath expressions (infeasible?), including any defaults. #### Other information workaround requires addition of an explicit (and unnecessary) coalesce() call to effectively initialize datetime function arguments to something that will result in a valid datetime string, just for the purpose of validation.

It seems if you try and convert the string fetched through a pulldata() function into a date, the XLSForm will not validate. And to calculate the number of days you will need to be use decimal-date-time() to convert your string. See this example:

Dates are tricky.

One person did a workaround of the javarosa issue by storing the date as a number in the csv (a number that is the same value that using decimal-date-time() on the date string would give). So in your CSV you would add a column with a formula such as
=(A3-DATE(1970,1,1))
playing_with_dates.xlsx (10.3 KB) and demo.csv (74 Bytes)

However, because of time of day, there are some oddities that emerge. For example when considering the time of day. Note the slight difference in number of days difference below.

To calculate the number of whole years, such as the age of a person, using the div 365.25 technique can sometimes error if the month and day of month are close. As noted in the first post I linked:

Note that I posted an exploration of that here with what (I think) should be an alternate way to calculate it: