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

Question

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...

FLORENTDEWAZIERES · Answer

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...

KimberlyGarbade · Answer

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()