topic Re: Cannot use SQL CAST function with layer.definitionExpression but it work fine with query builder in ArcGIS JavaScript Maps SDK Questions

Cannot use SQL CAST function with layer.definitionExpression but it work fine with query builder

FLORENTDEWAZIERES — Fri, 08 Apr 2022 15:14:25 GMT

Hi I wonder if someone had the same issue.

I'm working with features who needs to be filtered.

I need to use the definition expression : https://developers.arcgis.com/javascript/latest/api-reference/esri-layers-SceneLayer.html#definitionExpression

My problem is i want to use ISO dates so i'm using moment to get the iso and try to use :

date < CAST('2019-12-31T23:00:00.000Z' AS DATE)

It worked fine with query builder and also definition query in pro so im assuming it work on JS apis definitonExpression to but it said i use a non standard function.

I'm able to find a workaround by reformating my dates after convert with the .toIsoString() funciton and using but i still want to be clean and only convert to iso / sending a cast as date.

const dateMax = '2019-12-31 23:00:00.000' layer.definitionExpression = `dateStart < timestamp '${dateMax}'`

If anyone can help on this subject...

Re: Cannot use SQL CAST function with layer.definitionExpression but it work fine with query builder

KimberlyGarbade — Sat, 09 Apr 2022 08:03:29 GMT

I'm guessing it isn't working because your datastore is using a different RDBMS than your expecting.

If it's using PostGRE you could try the TO_DATE() or TO_TIMESTAMP() function. TO_DATE is just the DATE and TO_TIMESTAMP is (you guessed it) date and time, if you need to be that specific.

If it's SQL Server you could try CONVERT()

Re: Cannot use SQL CAST function with layer.definitionExpression but it work fine with query builder

FLORENTDEWAZIERES — Mon, 11 Apr 2022 07:18:10 GMT

You can't use these functions in the definition expression either. CAST is supposed to be the more basic way to do this in SQL...