Format Dates in a NetSuite Saved Search with TO_CHAR

The screenshots use sample data. Person and operating-company names are fictional. Your dates and display preferences may differ.

Before you start

You need permission to create or edit a Transaction saved search and access matching sales orders. This example uses a search called Sales Order Date Formats with three criteria: Type is Sales Order, Date within This Year, and Main Line is Yes. The date criterion limits these example records; it is not required for TO_CHAR. It also uses a custom body field called Promise Date whose type is Date. Find your account's actual field through the formula helper; its field ID and type may differ.

01. Build a date baseline

Go to Lists > Search > Saved Searches > New, select Transaction, enter the search title, and add the three criteria. On Results, keep Document Number and add the native Date field. Preview the search before adding formulas so you know which records and dates the text columns should match.

Enlarge

02. Add text formats

On Results, select Formula (Text) for a new row. Open its formula popup, enter TO_CHAR({trandate}, 'YYYY-MM-DD'), click Set, label the column ISO Date, and click Add. Preview it against the Date field: 15 October 2026 becomes 2026-10-15 in this example.

Enlarge

Add other format models as separate Formula (Text) rows, previewing each before the next. Use TO_CHAR({trandate}, 'YYYYMMDD') for a compact year-first string, TO_CHAR({trandate}, 'DD/MM/YYYY') for day-first display, and TO_CHAR({trandate}, 'YYYY') for a four-digit year. YEAR spells out the year and may pad it; DL returns a long date whose language and wording depend on account context. Fixed-width year-first text can sort chronologically, while day-first, YEAR, and DL text do not reliably do so. Keep Date for date-aware sorting, grouping, criteria, and calculations.

Enlarge

03. Format a custom date

Add another Formula (Text) row. In the formula popup, choose the custom date from the Field list, then keep the reference inserted by the helper. In this example it produces TO_CHAR({custbody_promise_date}, 'YYYY-MM-DD'). Click Set, label the result Promise Date ISO, and Add the row. Do not guess a custom field ID from its label.

Enlarge

Preview one populated custom date and one blank. The promise date can differ from the transaction Date, and a blank date stays blank. The field must be a real Date or Date/Time field; a text field containing a date needs a different approach. You can separately test TO_CHAR(SYSDATE, 'HH24:MI:SS') as Current Evaluation Time. That value is the database environment's current time at evaluation, not the order's creation time or a guaranteed user-local clock. It can change on each run and omits the date and timezone.

Enlarge

04. Check the saved result

Click Save & Run. Confirm the normal Date remains available beside the new text columns, and compare each format with a known order. If a formula fails, check the field type, helper-inserted reference, format model, and function support before saving it.

Enlarge

Check the result

1. The results show sales orders matching the configured criteria.
2. The native Date remains displayed alongside the formatted text columns.
3. The custom date result is blank for the order whose source field is blank.

Common questions

Why does a day-first formatted date sort out of order?

TO_CHAR returns text. DD/MM/YYYY sorts by its first characters, the day, rather than by the date value. Use a fixed-width year-first format for text sorting, and keep the native Date field for date-aware sorting.

How do I reference a custom date field?

Choose the field in the formula helper and use the reference it inserts. The label does not prove the script ID or field type. In this example, the Date field's reference is {custbody_promise_date}; your account may differ.

What happens when the custom date is blank?

TO_CHAR returns null, shown as a blank result. A placeholder such as “No date” needs an intentional text fallback, and that placeholder should not be used as a date for calculations.

Is SYSDATE the same as Date Created?

No. SYSDATE is evaluated when the search runs; Date Created is a stored record value. Use the latter when you need to report when the transaction was created.