NetSuite Saved Search – Reporting based on hours in the day

I needed a saved search to monitor activity. It needed to brake the last 2 days up into two slices, 1:00 PM yesterday to 8:00 AM today and 8:00 AM today until 1:00 PM today.

The point was to email activity occurring during the second half of the day yesterday to 8:00 AM today. And a second email with activity from 8:00 AM today through 1:00 PM today. I’m only showing the criteria for one of the two saved searches, the trick was knowing how to break out the hours of the day. I accomplished that using TO_CHAR({created}, ‘HH24’). This is the search criteria.

Notice the formula is type = numeric, so I can use “greater than” and “less than” comparisons, and I had to wrap the TO_CHAR() function inside a TO_NUMERIC() function. If you omit TO_NUMERIC(), everything appears to work, but hour “10” is then less than hour “8” in the formula, since the comparison is based on character strings and not numbers.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s