When working with NetSuite Saved Searches, you may need to filter records based on their creation date. A common requirement is to find records created within the past 12 months. This can be achieved using a Formula (Numeric) field in a NetSuite Saved Search.
Formula to Identify Records Created in the Last 12 Months: CASE WHEN {datecreated} >= ADD_MONTHS(TRUNC(SYSDATE), -12) THEN 1 ELSE 0 END
Understanding the Formula:
`{datecreated}` | The creation date of the record (e.g., transaction, customer, item).
`SYSDATE` | The current system date (today’s date).
`TRUNC(SYSDATE)` | Truncates the system date to remove the time portion, leaving only the date.
`ADD_MONTHS(TRUNC(SYSDATE), -12)` | Calculates the same date one year ago from today.
`{datecreated} >= ADD_MONTHS(TRUNC(SYSDATE), -12)` | Checks if the record was created on or after this date (i.e., within the last 12 months).
`CASE WHEN … THEN 1 ELSE 0 END` | If the condition is true, return `1` (record is within 12 months); otherwise, return `0`.