Effectively managing dates is a fundamental skill for any NetSuite user. From entering transaction dates correctly to building dynamic saved searches that automatically adapt over time, a solid understanding of date handling is crucial for accurate reporting and efficient workflows. This guide provides a fact-checked and expanded overview of date entry, relative date filters in saved searches, and powerful date-related SQL functions.

Date Entry and Formatting

NetSuite's date fields are flexible and can accept various input formats. While your display preference is set under Home > Set Preferences > General > Date Format, you can enter dates in several ways:

• Numeric formats: MM/DD/YYYY (e.g., 12/25/2024) or M/D/YY (e.g., 12/25/24)

• Text-based formats: Month DD, YYYY (e.g., December 25, 2024) or DD-Mon-YYYY (e.g., 25-Dec-2024)

NetSuite automatically parses these common formats when you type a date and press the Tab key. Alternatively, you can click the calendar icon next to any date field to select a date visually.

Dynamic Date Filtering in Saved Searches

To create reports and searches that remain relevant over time, you should use dynamic date filters instead of hard-coding specific dates. NetSuite offers two primary methods for this: Named Time Periods and Relative Date Ranges.

Named Time Periods (Dropdown Selections)

When you add a date filter to a saved search, the dropdown menu provides a rich list of predefined, dynamic date ranges. These are often referred to as "named time periods" or "rolling periods." The original article's list of keywords (`today`, `lastweek`, `thismonth`, etc.) accurately represents the options available in these dropdowns.

How to Use Named Time Periods:

1. In your saved search criteria, add a filter for a date field (e.g., Date, Transaction Date).

2. In the second dropdown list for the filter, you will see a list of options like the following:

   
   

Relative Date Ranges

For more specific ranges not covered by the named periods, you can define a relative date range.

How to Use Relative Date Ranges:

1. In the date filter's second dropdown, select relative.

2. This will reveal From and To fields where you can enter a number of days, weeks, months, quarters, or years ago or from now.

3. For example, to find transactions from 90 days ago up to today, you would set:

• From: 90 days ago

• To: 0 days ago

  
  

Advanced Date Calculations with SQL Formulas

For ultimate flexibility, you can use Oracle SQL functions within NetSuite formula fields to perform custom date calculations. The official NetSuite documentation provides a comprehensive list of supported functions.

Key Date and Time Functions

Example Formula: Days to Ship

To calculate the number of days between an order date and its ship date, you can use a simple subtraction formula in a Formula (Numeric) field:

{shipdate} - {trandate}

Example Formula: Identify Late Shipments

To create a checkbox that flags late shipments, you can use a CASE statement in a Formula (Checkbox) field:

CASE WHEN {shipdate} > {expectedshipdate} THEN 'T' ELSE 'F' END

Troubleshooting Common Date Issues

• Incorrect Date Display: If dates are not appearing in your preferred format, verify your settings under Home > Set Preferences > General > Date Format.

• Time Zone Discrepancies: Be aware that SYSDATE uses the server's time zone. Date filters in the UI, however, are adjusted for each user's time zone preference. Use TRUNC() to remove the time component from a date for more consistent comparisons across time zones.

Off-by-One Errors: When using date ranges, carefully check your filter logic. Using is on or after is different from is after and can affect which records are included.