How do I group a saved search by month, or format a date, in a formula?

Two expressions, and which one you want depends on whether the output has to stay a date. TO_CHAR({trandate},'YYYY-MM') in a Formula(Text) column gives you a month key you can group and sort on. TRUNC({trandate},'MM') in a Formula(Date) column gives you the first of the month as a real date value. Both were accepted with no error.

Minimal reproduction

Two expressions against the same transaction search, one results column per call, plus a third to show what the raw field holds:

Formula(Text)  TO_CHAR({trandate},'YYYY-MM')
               -> "2016-08"    "2016-04"

Formula(Date)  TRUNC({trandate},'MM')
               -> "8/1/2016"   "4/1/2016"

Formula(Text)  TO_CHAR({trandate},'YYYY-MM-DD HH24:MI:SS')
               -> "2016-08-29 00:00:00"

TO_CHAR honours the format mask exactly. Ask for YYYY-MM and you get four digits, a hyphen and two digits, nothing else. The third column is there because it settles a related question at the same time: {trandate} is stored as clean midnight, so nothing you do to it has to cope with a stray time component.

Which one to use

Use the text form when the value is a label, a grouping key or something you are going to export. YYYY-MM sorts correctly as plain text, because the year leads and the month is zero padded, so 2016-04 sorts before 2016-08 without any extra sort column. It is also the only one of the two whose written form is fixed by the formula rather than by a setting somewhere else in the account.

Use the date form when something after the column needs a real date: a comparison against another date field, or arithmetic. TRUNC({trandate},'MM') returns the first of the month, and it comes back typed as a date, not as text that looks like one.

The second element of TRUNC is the unit. 'MM' truncates to the first of the month. The run tested 'MM' and nothing else, so if you want quarters or years, confirm the mask in your own account rather than assuming from this page.

The catch with Formula(Date), and why text exports better

A Formula(Date) column is rendered through the account's date display format. In the account tested that is M/D/YYYY, which is why the same month that reads 2016-08 in the text column reads 8/1/2016 in the date column. Same underlying month, two different strings, and the difference comes from the account's display format rather than from anything in your formula.

That matters the moment something downstream parses the output. A CSV going into a spreadsheet, a script, or another system is now reading a format that came from the account's display setting rather than from your formula. Anything that reads 8/1/2016 as D/M/YYYY gets the eighth of January.

So the practical rule: if the column is going to leave NetSuite, use TO_CHAR with an explicit mask. You are choosing the format yourself instead of inheriting it.

What the run covered

The probe put both expressions into result columns of an ad hoc transaction search and read the values back. It did not exercise the full grouped round trip, so what is established here is that the expressions are accepted and what they return, not a measurement of a grouped report built on top of them.

Two things are worth knowing before you build that report. Summary types on a text formula column are not interchangeable: some are rejected outright with an error, and some are accepted and return a value that has no business meaning, which is a separate page in this section. And if the same report also carries a day count, {today} is a timestamp rather than a date and behaves nothing like {trandate} does here.

Scope: one sandbox account on NetSuite 2026.1, Administrator role. The searches were built and run ad hoc through the SuiteScript search engine, nlapiCreateSearch and runSearch, without saving anything. The date display format quoted is that account's setting, not a NetSuite default.