Solved! Go to Solution.
Thanks Luke.
Knowing that 'strftime' was the issue, I went back and relooked at creating the query string.
Essentially I was manipulating the field data before comparing it. So instead I left that as is and converted the comparison date to the same format. The field data is stored as a Julian Date, so I googled a function to convert a javascript date, injected that into the query and now I have it functional.
The convert to Julian function I took from here:
Calculating julian date with javascript - Stack Overflow
function toJulianDate(date) { var floor = Math.floor; var y = date.getUTCFullYear(); var m = date.getUTCMonth() + 1; var d = date.getUTCDate() + (date % 8.64e7) / 8.64e7; if (m < 3) { y -= 1; m += 12; } var a = floor(y/100); var b = date < new Date(Date.UTC(1582,9,15))? 0 : 2 - a + floor(a/4); var c = floor(y < 0? 365.25 * y - 0.75 : 365.25 * y); var e = floor(30.6001 * (m + 1)); return b + c + d + e + 1720994.5;}
and my SQL query is now:
`proposedRevisitDate >= ${toJulianDate(plannedRevisitDateFrom)}`
cheers,
-Paul
Hey Paul -
It's unclear where, but somewhere in our parser we are not handling this expression properly. We will need to look into it, but it does indeed look like a bug in our API.
Thanks,
Lucas
Looks like the issue is specifically with "sftrftime" - It is not SQL92 standard. Our platform has various SQL parsers, some which can and cannot handle this (sqlite, for example, does support this command). We will need to shore up the differences somehow.
SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation
Thanks Luke.
Knowing that 'strftime' was the issue, I went back and relooked at creating the query string.
Essentially I was manipulating the field data before comparing it. So instead I left that as is and converted the comparison date to the same format. The field data is stored as a Julian Date, so I googled a function to convert a javascript date, injected that into the query and now I have it functional.
The convert to Julian function I took from here:
Calculating julian date with javascript - Stack Overflow
function toJulianDate(date) { var floor = Math.floor; var y = date.getUTCFullYear(); var m = date.getUTCMonth() + 1; var d = date.getUTCDate() + (date % 8.64e7) / 8.64e7; if (m < 3) { y -= 1; m += 12; } var a = floor(y/100); var b = date < new Date(Date.UTC(1582,9,15))? 0 : 2 - a + floor(a/4); var c = floor(y < 0? 365.25 * y - 0.75 : 365.25 * y); var e = floor(30.6001 * (m + 1)); return b + c + d + e + 1720994.5;}
and my SQL query is now:
`proposedRevisitDate >= ${toJulianDate(plannedRevisitDateFrom)}`
cheers,
-Paul