Convert string to date?


I am developing a form that requires comparing the date of the start of treatment with the date 6 months ago: if treatment began six months ago or more, then another set of questions is relevant.

I have not been able to make this work in ODK Collect. I think it is because the month treatment began uses select_one type in order to allow for a “don’t know” response and the year is an integer. If the respondent does not remember the month, I assign the month of January. When I do that, it becomes a string variable. Then, I concatenate the month string with the year to make another string, adding the dashes so it looks like a date. These are then compared to a date variable of 6 months ago to determine whether the next set of questions apply. I have attached the .xlsx file.

When I validate it on XLSForm Online and view it in Enketo, it works. Apparently it can compare a string and a date variable successfully, which surprised me. However, it does not work in ODK Collect v1.18.2 on my Android 4.1.2 tablet with up to date software.

Any ideas about how to resolve this?

I feel like the answer lies in converting the string to date format. I have tried putting date() around the calculate expression for the date treatment began, but that fails the compiler. I have tried using format-date-time() for the date 6 months ago, but that also fails the compiler.

string_to_date.xlsx (12.9 KB)

In general, comparing dates directly is a little unreliable. I would use decimal-date-time() to convert the string and date first to number of days past Unix Epoch and see if the difference is roughly greater than 180 days.

It sounds like you need to construct your own date from a user-specified year, and optionally a user-specified month (or else January if unknown). And that you are attempting to do so with something like:

treatment_date: calculate = "concat(${year}, '-', ${month})"

which will give you a string that is not a valid date because all dates require a day as well, specifically "YYYY-MM-DD" (also note the month must be numeric, so "01" not "January"). Try this instead:

treatment_date: calculate = "concat(${year}, '-', ${month}, '-01')"

However I would echo @yanokwa in that anything involving two dates can get very ugly very quickly, if you are a perfectionist. So I'd likewise recommend taking the decimal-date-time() approach.

Thanks, Yaw. I'll give it a try.

I did that when it worked with Enketo. When I figured out that problem I hoped that would solve it, but not quite. I'll try the decimal-date-time. Thank you!

Hello all,

Sorry for bringing this conversation back but I didn't want to make a new one since my title would be the same.

My problem is a little different but what I want is to convert a string to a date. My date string is coming from a csv file that I read using the pulldata function. So my problem is that I don't even pass the conversion phase (from excel to xml). I've tried lots of stuff but no luck. Is this something possible in ODK?

The error I get on the offline converter is XPath evaluation: type mismatch converting to date

What exactly does your original date string look like (eg give examples)? Given the format, it should be possible to determine how to reformat it into a valid date (string)

@Xiphware thanks for prompt response, this is an example: 2019-10-03 00:00:00.0000000 but I can format it to be 2019-10-03 because I have full control of the data that is on the csv. The problem is that ODK doesn't allow this format-date(pulldata('visits_history', 'next_review_period_start_date', 'visit_key', ${visit_key}),'%d/%m/%y') I changed format-date to just date but nothing, even having the pulldata part in a separate calculated field

If you can get your raw data string into this format, then format-date(${mydate},'%d/%m/%y') should produce the desired result.

(but your have to get the date format exactly right, eg 0-prefixed 2-digit month and day)

Sorry but I think you are not understanding my problem. The issue is not on runtime, but when I try to convert the xls form to xml. The converter throws an error

Sorry, my misunderstanding... I suspect you might be hitting the issue described here: Which happened when you had a calculation as the input date to a date funtion (format-date(), decimal-date-time(), ...), the javaRosa parser threw a bogus error.

What tool are you using to convert your form to XML? If using XLSForm Offline, have you tried disabling Validation?

1 Like

Yes I'm using XLSForm Offline... Thanks for pointing that (disable validation) it was the solution, as you said JavaRosa has problem with what I was doing but ODK Collect on run time is ok with it.

Thanks Thanks Thanks


Glad we found a solution for you. ODK Validate gets it right 99% of the time, but this is one case it doesn't. If it runs in Collect, runs in Enketo, then your form is probably perfectly fine and you can disable validation without too much worry.

Hi there, I want to calculate date from one date variable and other numeric variable (decimal) which represents year. I am using the calculation as follows today () - ${dmdur}*365 the type of variable being calculate. Am I right and can I get the result in date format? To elaborate more, I want to calculate the DM diagnosis date from today () and duration of DM in year. Anyone who have experience in this issue?
thanks in advance.