ANS-0014 · SAVED SEARCHES & FORMULAS
NetSuite: Compare Dates Ignoring Time Using a Formula Numeric
This guide explains how to construct a formula numeric in NetSuite to compare two date/timestamp fields, ensuring only the date portion is considered.
Short answer
To compare NetSuite timestamps by date only, use a Formula (Numeric) field with the trunc() function. This function removes the time component, allowing for accurate date-only comparisons between fields like {custbody_date_proceed_sa} and sysdate, returning 1 for true and 0 for false based on your criteria.
Scenario
When comparing two timestamp fields in NetSuite, such as a custom date field and the current system date, it is often necessary to disregard the time component. Standard comparisons might yield inaccurate results if the time of day differs, even if the dates are the same. A method is required to perform a date-only comparison.
Solution
To compare NetSuite timestamps while disregarding the time component, a Formula (Numeric) field can be utilized. This approach employs the trunc() function, which truncates a date or timestamp to the beginning of the day, effectively removing the time portion. The following formula can be implemented:CASE WHEN trunc({custbody_date_proceed_sa}) - trunc(sysdate) <= 2 THEN 1 ELSE 0 ENDThis formula returns 1 if the difference between the truncated custom date field ({custbody_date_proceed_sa}) and the truncated system date (sysdate) is less than or equal to 2 days, and 0 otherwise.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support