1

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.

enter image description here

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

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

André
  • 750
  • 11
  • 24
  • 1
    BTW don't mind the localhost links, we are using a authenticating proxy. That proxy adds the headers. This is a real NetSuite call. – André Jul 26 '23 at 14:49
  • Did you found the correct way? I am also facing same issue. Need to urgently find a fix – mohit singla Sep 01 '23 at 13:24
  • I am doing something like { "q": "SELECT id, lastModifiedDate FROM transaction where lastModifiedDate <= TO_TIMESTAMP( '2023-08-25 18:27:00.000000000', 'YYYY-MM-DD HH24:MI:SSxFF' ) and NOT tranId LIKE 'IF-%' order by lastModifiedDate desc" } But still I am getting "2023-08-25 18:30:00" in results – mohit singla Sep 02 '23 at 14:25
  • Strange the HH24 fixed it for me... Maybe a Time-Zone thing? What happens if you add: ' TZH:TZM' and use that as well in your query. NetSuite documentation is rather ppor on this ... – André Sep 02 '23 at 18:39

1 Answers1

0

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"
}
André
  • 750
  • 11
  • 24