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

1921
5
Jump to solution
04-25-2022 02:58 PM
js80540
New Contributor II

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.

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
DeonLengton
Esri Contributor

Hi

I simplified it and this is the result:

DeonLengton_0-1650967598969.png

 

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.

datetest_dobDate of birth

 

The second calculation adds 21 days to the test_dob date.

Hope that helps.

View solution in original post

0 Kudos
5 Replies
DougBrowning
MVP Esteemed Contributor

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

0 Kudos
js80540
New Contributor II

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!

0 Kudos
DougBrowning
MVP Esteemed Contributor

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

0 Kudos
DeonLengton
Esri Contributor

Hi

I simplified it and this is the result:

DeonLengton_0-1650967598969.png

 

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.

datetest_dobDate of birth

 

The second calculation adds 21 days to the test_dob date.

Hope that helps.

0 Kudos
js80540
New Contributor II

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!

0 Kudos