SuiteQL date functions: the ones you actually need

Dates are where correct-looking SuiteQL goes wrong. The query runs, returns rows, and is short by a day or a month or one late-entered invoice. SuiteQL date functions are Oracle's, so the functions themselves are well behaved — the problems come from NetSuite's schema, where some columns carry a time and some do not, and where the date on a transaction is not the period it belongs to.

The function list, and what each is for

Rule one: half-open ranges, never BETWEEN

BETWEEN is inclusive on both ends, which is fine on a pure date column and wrong on anything with a time. createddate BETWEEN TO_DATE('2026-06-01','YYYY-MM-DD') AND TO_DATE('2026-06-30','YYYY-MM-DD') matches nothing created after midnight on 30 June — so it silently loses a day of records, every time, in every report built on it.

-- Wrong: loses everything on 30 June after 00:00
SELECT COUNT(*) FROM transaction
WHERE createddate BETWEEN TO_DATE('2026-06-01', 'YYYY-MM-DD')
                      AND TO_DATE('2026-06-30', 'YYYY-MM-DD');

-- Right: >= start of period, < start of next period
SELECT COUNT(*) FROM transaction
WHERE createddate >= TO_DATE('2026-06-01', 'YYYY-MM-DD')
  AND createddate <  TO_DATE('2026-07-01', 'YYYY-MM-DD');

Write every date filter this way and you never have to remember which columns carry a time. The upper bound is the first instant you do not want.

Rule two: transaction date is not the accounting period

This is the difference between a query that agrees with finance and one that does not. trandate is the document date. postingperiod is the accounting period the GL effect landed in. An invoice dated 31 January and entered on 3 February, after January closed, has a January trandate and a February posting period. Filter on trandate and your monthly revenue will never tie to the trial balance.

SELECT
    ap.periodname,
    ap.startdate,
    ap.enddate,
    SUM(-tal.amount)            AS revenue
FROM transactionaccountingline tal
JOIN transaction      t  ON t.id  = tal.transaction
JOIN accountingperiod ap ON ap.id = t.postingperiod
JOIN account          a  ON a.id  = tal.account
WHERE t.posting   = 'T'
  AND ap.isadjust = 'F'
  AND ap.isquarter = 'F'
  AND ap.isyear    = 'F'
  AND a.accttype IN ('Income', 'OthIncome')
  AND ap.startdate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -6)
GROUP BY ap.periodname, ap.startdate, ap.enddate
ORDER BY ap.startdate

Three period filters earn their place. isadjust = 'F' keeps period 13 out of a monthly series. isquarter and isyear exclude the summary rows in the period hierarchy, which would otherwise double-count everything inside them. Revenue is negated because income accounts post as credits. Whether your own account needs those exact flags depends on how your fiscal calendar was built, so check the period list once and reuse the filter forever.

Rule three: bucket by month with TRUNC, not by string

SELECT
    TRUNC(t.trandate, 'MM')             AS month_start,
    TO_CHAR(t.trandate, 'YYYY-MM')      AS month_label,
    COUNT(DISTINCT t.id)                AS invoices,
    SUM(tl.netamount)                   AS invoiced_value
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type = 'CustInvc'
  AND tl.mainline = 'F'
  AND t.trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12)
  AND t.trandate <  TRUNC(SYSDATE, 'MM')
GROUP BY TRUNC(t.trandate, 'MM'), TO_CHAR(t.trandate, 'YYYY-MM')
ORDER BY month_start

Group by the truncated date and label with TO_CHAR. Grouping only on the string works but sorts alphabetically, which is fine for 'YYYY-MM' and breaks the moment someone asks for 'Mon YYYY'. The upper bound excludes the current partial month, so the chart does not end in a misleading dip. Calendar months are not fiscal periods — if your fiscal year starts in April or you run 4-4-5, use the accountingperiod join instead.

Rule four: age things against a truncated SYSDATE

SELECT
    BUILTIN.DF(t.entity)                        AS customer,
    t.tranid                                    AS invoice_no,
    t.trandate,
    t.duedate,
    TRUNC(SYSDATE) - t.duedate                  AS days_overdue,
    CASE
      WHEN t.duedate IS NULL                        THEN 'no due date'
      WHEN TRUNC(SYSDATE) - t.duedate <= 0          THEN 'current'
      WHEN TRUNC(SYSDATE) - t.duedate <= 30         THEN '1-30'
      WHEN TRUNC(SYSDATE) - t.duedate <= 60         THEN '31-60'
      WHEN TRUNC(SYSDATE) - t.duedate <= 90         THEN '61-90'
      ELSE '90+'
    END                                         AS aging_bucket,
    t.foreigntotal
FROM transaction t
WHERE t.type   = 'CustInvc'
  AND t.status = 'CustInvc:A'
ORDER BY days_overdue DESC

TRUNC(SYSDATE) rather than bare SYSDATE, so the day count is a whole number instead of a fraction that puts an invoice due today into the 1-30 bucket. duedate can be null on transactions with no terms, so the CASE handles it explicitly rather than letting those rows fall into '90+' through null arithmetic. Feed the output into the AR aging calculator for bucket mix, weighted average days overdue and a reserve estimate.

Rule five: MONTHS_BETWEEN returns a fraction

SELECT
    c.entityid,
    c.companyname,
    c.datecreated,
    FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), c.datecreated))    AS tenure_months,
    (SELECT FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), MAX(t.trandate)))
       FROM transaction t
      WHERE t.entity = c.id
        AND t.type   = 'SalesOrd')                          AS months_since_order
FROM customer c
WHERE c.isinactive = 'F'
  AND c.datecreated < ADD_MONTHS(TRUNC(SYSDATE), -12)
ORDER BY months_since_order DESC NULLS FIRST

MONTHS_BETWEEN returns a decimal — 14.7419 rather than 14 — so wrap it in FLOOR when you want whole months. ADD_MONTHS handles month-end properly, giving 28 February for a year before 29 February, which naive day arithmetic gets wrong. NULLS FIRST surfaces customers who have never ordered, who are usually the interesting ones.

Rule six: dates stored as text need explicit conversion

SELECT
    r.id,
    r.custrecord_contract_ref                                       AS contract_ref,
    r.custrecord_renewal_date_text                                  AS raw_value,
    TO_DATE(r.custrecord_renewal_date_text, 'DD/MM/YYYY')           AS renewal_date,
    TO_DATE(r.custrecord_renewal_date_text, 'DD/MM/YYYY')
        - TRUNC(SYSDATE)                                            AS days_to_renewal
FROM customrecord_service_contract r
WHERE r.custrecord_renewal_date_text IS NOT NULL
  AND TO_DATE(r.custrecord_renewal_date_text, 'DD/MM/YYYY')
        BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 90
ORDER BY renewal_date

This works and it is fragile. One row with 31/13/2026 or an empty string that is not null and the whole query fails on conversion, because the error happens per row during evaluation. If you inherit a text date field, the durable fix is a real date field and a one-off data cleanse, not a cleverer query. BETWEEN is safe here because both sides are already truncated dates.

Timezones, and why two people get two answers

NetSuite renders date/time values according to a timezone setting, and the value you see in the UI is not necessarily the value the query engine compares. The practical consequences are narrow but real.

• A record created late in the evening can appear on either side of a day boundary depending on whose timezone is applied. Same-day filters on createddate are the ones affected.
• SYSDATE reflects the database server's clock, not your desk. For anything where a one-day shift matters, pass the boundary date in as an explicit parameter rather than deriving it from SYSDATE.
• trandate is date-only, so it is immune. This is a good reason to prefer it for business reporting and reserve createddate for audit questions.
• Reports that must reconcile across regions should be defined on accounting periods, which have unambiguous boundaries, rather than on timestamps.

A checklist for any date filter you write

1. 1.Is this a business question or an audit question? Business means trandate or posting period. Audit means createddate or lastmodifieddate.
2. 2.Does the answer have to tie to the GL? If yes, join accountingperiod on postingperiod and stop using trandate.
3. 3.Is the range half-open? >= start and < next start, always.
4. 4.Are adjustment and summary periods excluded? isadjust, isquarter and isyear all filtered to 'F' for a monthly series.
5. 5.Is the bound relative rather than hard-coded? ADD_MONTHS(TRUNC(SYSDATE,'MM'), -6) never needs editing; '2026-01-01' needs editing in January.
6. 6.Have you formatted it? Multi-clause date logic is unreadable on one line — the SuiteQL formatter sorts that out in a second.

If the join syntax in the examples above needs unpacking, that is covered in SuiteQL join examples, and the period-alignment questions that come up at close are in the NetSuite month-end close checklist. Beginners should start with SuiteQL tutorial for beginners.

The reason date logic deserves to be visible

Nothing in this post throws an error. A BETWEEN that loses a day, a trandate filter where the period was meant, a fractional MONTHS_BETWEEN bucketing an invoice one band too early: all of them return a confident number. That is why a number without its query attached is not worth much.

ERPray prints the SuiteQL under every answer for exactly this reason. Ask what did we invoice by period for the last six months and you can read whether it used posting period or transaction date, whether adjustment periods were excluded, and whether the range was half-open — before you paste the figure into a board deck. Read-only by default, NetSuite-first, running as a dedicated instance you can self-host.