Problems querying items using lastmodifed in SuiteQL

Question

I am having strange issues querying NetSuite using SuiteQL.

I am using a query based on TO_DATE: where lastmodifieddate > TO_DATE( '2023-03-01 05:24:25', 'YYYY-MM-DD HH:MI:SS' ) order by lastmodifieddate for some reason NetSuite SuiteQL is only using the date part in the WHERE clause.

This seems to work: But this way I guess I won't get an index search:

Has anyone tried anything like this? I am trying to get items changed after a certain timestamp. But I only get update from a certain date ...

Answer 1

OK... It is an Dateformat thing ....

YYYY-MM-DD HH24:MI:SSxFF is the "normal" way of doing things. Extra issue: it is the local time of the NetSuite instance. TO_CHAR ( lastmodifieddate, 'YYYY-MM-DD HH24:MI:SSxFF TZH:TZM' ) results in 2023-03-01 09:10:19.000000000 +01:00

{
             "q": "SELECT id, itemtype, TO_CHAR ( lastmodifieddate, 'YYYY-MM-DD HH24:MI:SSxFF' ) as lastmodifieddate FROM item where lastmodifieddate > TO_TIMESTAMP( '2023-03-01 08:43:58.000000000', 'YYYY-MM-DD HH24:MI:SSxFF' ) order by lastmodifieddate"
}