SuiteQL EXTRACT: Get the Year and Month From a Date

SuiteQL can extract the year or month component from a NetSuite date field using Oracle-style EXTRACT syntax:

EXTRACT(YEAR FROM transaction.trandate)
EXTRACT(MONTH FROM transaction.trandate)

This is useful when you need to filter records to a calendar month, group results by year, or compare a record date with CURRENT_DATE.

Extract the year from a SuiteQL date

SELECT
    transaction.id,
    transaction.tranid,
    transaction.trandate,
    EXTRACT(YEAR FROM transaction.trandate) AS transaction_year
FROM transaction
WHERE transaction.trandate IS NOT NULL

transaction_year is numeric, so it can be compared with a number:

WHERE EXTRACT(YEAR FROM transaction.trandate) = 2026

Extract the month

The month result is also numeric, from 1 through 12:

SELECT
    transaction.id,
    transaction.trandate,
    EXTRACT(MONTH FROM transaction.trandate) AS transaction_month
FROM transaction
WHERE EXTRACT(MONTH FROM transaction.trandate) = 7

The value is 7, not the string '07' and not the month name 'July'.

Filter the current calendar month

Compare both components with CURRENT_DATE:

SELECT
    transaction.id,
    transaction.tranid,
    transaction.trandate
FROM transaction
WHERE EXTRACT(YEAR FROM transaction.trandate)
        = EXTRACT(YEAR FROM CURRENT_DATE)
  AND EXTRACT(MONTH FROM transaction.trandate)
        = EXTRACT(MONTH FROM CURRENT_DATE)

Comparing the year as well as the month is essential. Filtering only on month 7 would return July records from every available year.

Oracle lists CURRENT_DATE among SuiteQL’s supported SQL functions. It returns the current date in the session time zone, so it can differ from a UTC timestamp around the boundary of a day.

Filter with a SuiteScript bind parameter

When the desired date comes from SuiteScript, bind a formatted string and turn it into a SQL date with TO_DATE:

const selectedDate = '2026-07-20';

const results = query.runSuiteQL({
  query: `
    WITH inputs AS (
      SELECT TO_DATE(?, 'YYYY-MM-DD') AS selected_date
      FROM DUAL
    )
    SELECT
      transaction.id,
      transaction.trandate
    FROM transaction
    CROSS JOIN inputs
    WHERE EXTRACT(YEAR FROM transaction.trandate)
            = EXTRACT(YEAR FROM inputs.selected_date)
      AND EXTRACT(MONTH FROM transaction.trandate)
            = EXTRACT(MONTH FROM inputs.selected_date)
  `,
  params: [selectedDate],
}).asMappedResults();

Using TO_DATE(?, 'YYYY-MM-DD') makes the expected string format explicit. It also avoids passing a JavaScript Date object as a SuiteQL bind value.

Group results by year and month

Every non-aggregated expression selected alongside COUNT, SUM or another aggregate needs to appear in GROUP BY:

SELECT
    EXTRACT(YEAR FROM transaction.trandate) AS transaction_year,
    EXTRACT(MONTH FROM transaction.trandate) AS transaction_month,
    COUNT(*) AS transaction_count
FROM transaction
WHERE transaction.trandate IS NOT NULL
GROUP BY
    EXTRACT(YEAR FROM transaction.trandate),
    EXTRACT(MONTH FROM transaction.trandate)
ORDER BY
    transaction_year,
    transaction_month

This produces one row per calendar month.

Use `TO_CHAR` when you need a display value

EXTRACT is a good fit for numeric comparisons. Use TO_CHAR when you need a formatted label:

SELECT
    TO_CHAR(transaction.trandate, 'YYYY-MM') AS transaction_month,
    TO_CHAR(transaction.trandate, 'Month') AS month_name
FROM transaction

Common patterns include:

Expression	Example result
`EXTRACT(YEAR FROM trandate)`	`2026`
`EXTRACT(MONTH FROM trandate)`	`7`
`TO_CHAR(trandate, 'YYYY-MM')`	`2026-07`
`TO_CHAR(trandate, 'Mon')`	`Jul`

For equality comparisons covering one month, TO_CHAR(date, 'YYYY-MM') can be shorter:

WHERE TO_CHAR(transaction.trandate, 'YYYY-MM')
    = TO_CHAR(CURRENT_DATE, 'YYYY-MM')

Consider a date range for large queries

Applying a function to every row’s date may make it harder for the database to use an efficient access path. For large datasets, an explicit date range can be clearer and faster:

WHERE transaction.trandate >= TO_DATE('2026-07-01', 'YYYY-MM-DD')
  AND transaction.trandate <  TO_DATE('2026-08-01', 'YYYY-MM-DD')

Use an inclusive lower bound and an exclusive upper bound. That pattern also works safely when a field includes a time component.

Calendar year is not accounting period

EXTRACT(YEAR FROM ...) returns the calendar year of the date. It does not return a NetSuite fiscal year or accounting-period name.

When the requirement is based on posting periods, join the appropriate accounting-period record and filter that period explicitly instead of assuming calendar months match the organisation’s fiscal structure.

Common mistakes

Comparing the numeric month with a padded string such as '07'.
Filtering by month without also filtering by year.
Passing a JavaScript Date directly as an N/query parameter.
Using calendar-year extraction for a fiscal-period requirement.
Selecting extracted values with an aggregate but omitting them from GROUP BY.
Formatting dates for display when a bounded date-range filter would be more efficient.