Why does my SuiteQL date come back as 8/20/2026 with no time?

The value comes back rendered as M/D/YYYY and the time is discarded on the way out, though it still exists in the column. Select TO_CHAR(col,'YYYY-MM-DD HH24:MI:SS') to get the full timestamp. A TO_DATE format mask parses input; it does not control output.

Minimal reproduction

SELECT id, lastmodifieddate, TO_CHAR(lastmodifieddate, 'YYYY-MM-DD HH24:MI:SS') AS s
FROM transaction ORDER BY lastmodifieddate DESC FETCH FIRST 3 ROWS ONLY

Three rows came back. On all three, the raw lastmodifieddate was the same date-only string, "8/20/2026", with no time of day in it at all. The s column is TO_CHAR of that same column on that same row, and its three values were full timestamps to the second, hours apart from each other.

Same column, same row, two different amounts of information. Rows modified hours apart are indistinguishable in the raw payload.

The fix

SELECT id, TO_CHAR(lastmodifieddate, 'YYYY-MM-DD HH24:MI:SS') AS modified_at
FROM transaction ORDER BY lastmodifieddate DESC FETCH FIRST 100 ROWS ONLY

Return the TO_CHAR string, not the raw column, anywhere a timestamp matters. ORDER BY on the raw column is fine, it sorts on the full underlying value; it is only the returned value that is truncated.

Why this happens

Two separate things are going on, and only one of them is measured broadly.

The time being dropped is measured on transaction.lastmodifieddate. The time exists in the stored column, it survives sorting, and it is absent from the serialised value. That is one column on one table in one account, and it is the only place this run demonstrated a time component being discarded, because it is the only place a time component was shown to exist in the first place.

The M/D/YYYY rendering is everywhere the run returned a date without formatting it. SELECT SYSDATE FROM DUAL gives "8/20/2026". TRUNC(SYSDATE) gives a byte-identical "8/20/2026", so you cannot see whether TRUNC did anything. TO_DATE('2026-01-15','YYYY-MM-DD') comes back as "1/15/2026", so the mask governed parsing and not output. TO_CHAR with an explicit mask is the one form that rendered exactly what it was asked for.

Where evidence stops: the run did not open Setup and confirm which preference drives the rendering. That it follows the account or session date display preference is inference from the format matching this account's, not a verified setting. One production account on NetSuite 2026.1. Entirely through N/query.runSuiteQL; not tested on the REST endpoint.

One more thing that bites in the same place: stored datetimes are not on the SYSDATE clock, and SYSDATE and CURRENT_DATE came back ten hours apart in a single row.

How to tell if you are affected

Anchor the test to one row. Comparing raw dates across several rows proves nothing, because in a quiet account those rows can legitimately fall on different days and the raw values then differ whether or not the time is being dropped.

SELECT lastmodifieddate AS raw_value, TO_CHAR(lastmodifieddate,'YYYY-MM-DD HH24:MI:SS') AS s
FROM transaction ORDER BY lastmodifieddate DESC FETCH FIRST 1 ROWS ONLY

Both columns are the same column on the same row, so the only difference between them is the rendering. ORDER BY ... DESC puts the most recently touched record first, which is what keeps its time of day away from midnight and keeps the comparison meaningful.

If raw_value carries a date only while s carries a clock time, the raw value is losing the time, and every downstream consumer of it has day granularity. If raw_value carries the same clock time that s does, you are not affected. An incremental sync that watermarks on a truncated value re-reads or skips a whole day, with no error and no warning.