How do I compare a date column to a date literal in SuiteQL?

TO_DATE('2026-01-01','YYYY-MM-DD'), DATE '2026-01-01' and the bare string '1/1/2026' all work and returned identical counts. The bare ISO string '2026-01-01' errors out. BETWEEN with TO_DATE on both sides works.

Minimal reproduction

The form most people try first is the one that fails:

SELECT COUNT(*) AS n FROM transaction WHERE trandate >= '2026-01-01'

N/query.runSuiteQL error: Search error occurred: Invalid or unsupported search

That error text is verbatim NetSuite. Through SuiteScript it arrives wrapped in the N/query.runSuiteQL error: prefix the module adds; everything after that prefix is NetSuite's own string.

The fix

SELECT COUNT(*) AS n FROM transaction
WHERE trandate BETWEEN TO_DATE('2026-01-01','YYYY-MM-DD')
                   AND TO_DATE('2026-01-31','YYYY-MM-DD')

All five forms, as tested:

Form	Result
trandate >= TO_DATE('2026-01-01','YYYY-MM-DD')	works
trandate >= DATE '2026-01-01'	works
trandate >= '2026-01-01'	error, Invalid or unsupported search
trandate >= '1/1/2026'	works
trandate BETWEEN TO_DATE(...) AND TO_DATE(...)	works

The three >= forms that ran returned exactly the same count as each other, so they are the same predicate written three ways, with no silent disagreement between them.

Why this happens

Implicit string-to-date coercion happens for some bare literals and not others. '1/1/2026' coerced and counted; '2026-01-01', the same calendar date, was refused outright. Those two strings differ in field order and in separator, and no third spelling was tried, so which of the two differences the engine objected to is not established. What is established is that one spelling of a bare literal works in this account and another one errors.

The refusal is not a parse error either. It is the generic Invalid or unsupported search that also comes back from CAST(id AS VARCHAR(20)), so the message tells you nothing about which part of your query the engine disliked.

The bare form that worked matched this account's M/D/YYYY display format. One account, one date preference, so read that as the reason to write the explicit form rather than as a demonstrated locale rule. An explicit TO_DATE or DATE literal does not raise the question at all.

Output formatting is a separate question from input parsing. TO_CHAR(trandate,'YYYY-MM-DD') renders ISO on the way out, while a TO_DATE mask affects parsing only and its result still renders as M/D/YYYY.

Scope: one production account on NetSuite 2026.1. Entirely through N/query.runSuiteQL; not tested on the REST endpoint.

How to tell if you are affected

Test with a date whose day and month cannot be confused with each other, and pick one that sits in the middle of your own transaction history so that rows exist on both sides of it. 1 January is the same date in every field order, which is exactly why it cannot be the test.

Run these two statements one at a time and compare the counts. Substitute a date from inside your own data if 2020 is empty for you.

SELECT COUNT(*) AS n FROM transaction WHERE trandate >= '1/12/2020'

SELECT COUNT(*) AS n FROM transaction WHERE trandate >= TO_DATE('2020-01-12','YYYY-MM-DD')

Equal counts mean your account read '1/12/2020' as 12 January 2020, the same date the explicit literal names, so month comes first there. Different counts mean it was read as 1 December 2020, and every bare literal in your codebase is silently addressing a date other than the one you wrote wherever the two readings diverge. An error on the first statement means bare literals in that spelling are refused outright in your account, which is the loud failure and the harmless one.