This article is relevant if you are programming in NetSuite’s SuiteScript and you are having trouble searching on a Date/Time field.
Background
NetSuite offers a Date/Time data type that holds both the Date and the Time portion. Unfortunately, it doesn’t appear you can search on this data type even when using NetSuite’s provided nlapiDateToString(date, ‘datetimetz’) function call. You may experience a system generated ‘UNEXPECTED_ERROR’ error message when you run the nlapiSearchRecord function.
Solving NetSuite Date/Time Search Filter
To solve this challenge, I elected to create a custom integer field representing JavaScript’s capacity to change a Date field to an integer representing the number of milliseconds since midnight January 1, 1970 UTC.
Here is what it looks like:
//code snippet for JavaScript class structure to return timestamp information
var timestamp = null;
//here we are expecting this.Timestamp to represent a JavaScript date, not a NetSuite formatted date.
this.__defineGetter__("Timestamp", function(){return timestamp; });
this.__defineSetter__("Timestamp", function(val){timestamp = new Date(val);});
//return the integer value of the date
this.__defineGetter__("TimestampJS", function(){
if (this.Timestamp){return this.Timestamp.valueOf();
}; return this.Timestamp; });
//later code snippet for use in NetSuite filter array
var filters = new Array();
filters[0] = new nlobjSearchFilter('custrecord_timestamp_js', null, 'equalto', this.TimestampJS);
Get Help Inventing with NetSuite
Check out my other article for parsing dates in NetSuite.
While I suspect there are other techniques to resolve searching on NetSuite date/time formats, I welcome conversation. Share your thinking below. If you need help extending and inventing with NetSuite, contact us.
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
There’s some funky things going on with the custom date/time fields. They are still a WiP, I think. I just closed a case that I had been working with NS for 13 months on a searching defect with these.
I had just been wanting to calculate the time difference between the custom field and the incident date on a case. Pretty straightforward, you’d think. I ended up (again after 13 months) with a formula, using an undocumented NS function, that works. I use this:
(cast(nl_tz.convert({custom_field}) as date) – {date})*1440
There might be similar issues with searching in script. I know trying to import these they have to have the seconds :ss or they won’t import.
The last ‘buggy’ thing with them that I’ll bring up is how they show in the system notes a different time being set every time someone from another timezone edits a record. It’s confusing to people.
Corey,
Thanks for the comment. That is good insight and helps ground the situation. I was a bit concerned that I may have missed something. But I spent probably about 2.5 hours on it before I gave up and invented a solution. That casting formula: nl_tz.convert() appears to be a custom Oracle structure that probably can come in handy for other uses.
What I like about the JavaScript primitive approach is that it gets down to the millisecond. Since it is just an integer, it is easy to perform math on it. And if you feed it to a standard JavaScript Date function, it reconstitutes.
Very handy to think about the System Notes and Dates/Timezones. That sounds like a possible future article. Do you have a screen shot of it and what we need to do to see records clearly?
The time/date in the systems notes is very confusing I agree. NS really ought to put the timezone abbreviation next to the timestamp so we can tell. This should be everywhere a time/date is displayed. Some of the dates are adjusted for the user’s timezone preference set on their home > preferences. But others are buggy like the system notes, where you can see chronology out of order due to the timezones of different users making changes.
The nl_tz.convert() was an internal function used by NS until a few weeks ago when it was released to use. It allows for actually using the custom fields in a search effectively. It brought back an error prior to that.
Here is a sample screen shot of a case system notes filtered for only the field. This is from one of our east coast support reps. Every time it was edited from a west coast rep in changed in the system notes. The underlining time never changed, of course but it was confusing for people.
Let me know if my image didn’t show up.
Got the image. Thank you Corey!
I am trying to compare two fields on a custom report. One field is “Time Open” and the other field is “Case closure goal”. The “Case Closure Goal” field in an integer and the “Time Open” field is stated in Days and Hours. (4 days 21 hours). Is there a way to convert the “Time Open” to a single integer in number of hours? (117 hours). I am trying to get a report field that will let me know if I met the “Case Closure Goal”
If anyone can give me some direction on this I would really appreciate it.
Thanks,
Additional info I should have included in my original post.
I want to be able to subtract the “Time Open” hours from the “Case Closure Goal” hours and if the result is positive then I met the “Case Closure Goal” If the number is negative then I didn’t meet the goal. It the custom report I can only use the difference function on two fields that are integers so that is why I want to convert the “Time Open” hours from (4 days 21 hours) to a single integer (117).
Thanks again
Hi Gary,
It sounds like you are trying to do this within a Saved Search. Have you tried working with Oracle’s PLSQL to_char and to_number functions to format the date ultimately to a number? Here is a reference:
https://www.techonthenet.com/oracle/functions/to_char.php
Marty
To those looking for an answer for 2016 you can use the (ONOR)BEFORE (ONOR)AFTER search operators to search with date strings in m/d/yy format. It also accepts the mm/dd/yyyy format.
This will work for SuiteScript 1.0 as of today:
var search = nlapiSearchRecord('item', quantity_changed_search, new nlobjSearchFilter('lastquantityavailablechange',
null,
'within',
['3/21/2020 12:00 am',
'4/10/2020 12:00 am']))