topic Re: Pull date from csv and use in calculate (type mismatch) in ArcGIS Survey123 Questions

Pull date from csv and use in calculate (type mismatch)

js80540 — Mon, 25 Apr 2022 21:58:55 GMT

Hello,

I have seen this question pop-up a few times but never really any official answer.

I am pulling a date ("2021-04-01") from a csv file, which I would like to ingest in a form as a date object (using date()) in order to add 21 days to it and check against current date.

I am stuck at the first step though, I can't seem to be able to convert the string of a date to a date object.

So with my date being pulled from the csv and equal to '2022-04-01', stored as test_dob. date(string(${test_dob})) returns mismatch error, but if I do date(string('2022-04-01)) it works no problem.

So it makes me wonder how this date actually get stored in the back-end. And why it displays perfectly but doesn't get ingested as expected in the date function.

I have attached a csv sample and the table from which i am trying to pull (names are fake by the way). I am using the last S123 Connect

Thank you very much for your help.

Re: Pull date from csv and use in calculate (type mismatch)

DougBrowning — Mon, 25 Apr 2022 22:17:12 GMT

My guess would be using the word Date as your column name. That is a reserved word in SQL lite. Try changing it.

Re: Pull date from csv and use in calculate (type mismatch)

js80540 — Mon, 25 Apr 2022 22:38:30 GMT

I actually just changed it for the sake of clarity before posting here, this is not the source of the issues unfortunately. Here are two updated files renamed.
Thank you!

Re: Pull date from csv and use in calculate (type mismatch)

DougBrowning — Mon, 25 Apr 2022 23:05:06 GMT

Yea i cant get a pulldata and date to play nice either. Did you try doing a note, take off the date() part then just bind esri type to date? Might work.

Looks like date may need a certain format also.

date(question, expression, or value)

Converts a number or string to a date object, without preserving time.

date('2017-05-28T04:39:02+10:00')

Sorry all I got

Re: Pull date from csv and use in calculate (type mismatch)

DeonLengton — Tue, 26 Apr 2022 10:08:11 GMT

I simplified it and this is the result:

I hope that is the outcome you were hoping for. Here are the calculations:

pulldata('permits','date_to_pull','name',${permit_number})

${test_dob}+21*24*60*60*1000

The first one is still as you had it but i changed it to be a date type.

date

test_dob

Date of birth

The second calculation adds 21 days to the test_dob date.

Hope that helps.

Re: Pull date from csv and use in calculate (type mismatch)

js80540 — Tue, 26 Apr 2022 16:15:04 GMT

Oh wow! Thank you!
I really wish this was mentioned in the documentation, a few other people seem to have the same issue on the forum since the documentation suggest using date().

Thanks again!