At the risk of repeating information everybody already knows, these are two things worth knowing: • Treat 

raw

 like a reserved word. I found that one cannot use a column alias that ends in the keyword 

raw

. As one sees with N.query.toSuiteQL().query, the underlying engine uses 

raw

 (case insensitive) in mapping query tokens and results. Example: column alias 

inv_trandate_raw

 gets stripped to 

inv_trandate_

 (no error, however). • runSuiteQL() date columns are (A) stored/interpreted as datetime in UTC under the hood and (B) expressed in search result sets as localized strings. ◦ Issues (to my mind) ▪︎ The good news is that SuiteQL appears to use UTC for localization always, so that's predictable. ▪︎ The bad news is that a trandate you know as (say) 7/21/2021 in your Pacific Time Zone localized account will appears 7/22/2021 in your SuiteQL search result set. Fun times. ▪︎ The worse news it that the formatting of the string is localized to the prevailing localization settings (whether user or account/system). ◦ Solutions ▪︎ N/format - thanks, no thanks. Can you use N/format? Not sure. I wouldn't. You shouldn't have to. It would help if critical scenarios received due treatment in the documentation. I'm uncomfortable with N/format, b/c I don't want to get strings that have hidden time values or really any tz-ignorant datatype when I'm dealing with dates/datetimes. It's 2021. This hasn't been an issue since Java 8. What gives? IMHO SuiteScript APIs should give Date objects for dates/datetimes always. It's egregious to give strings, particularly when the Oracle functions for timezone-sensitive datetime handling are unavailable. Oracle database. Oracle system. Oracle data centers. Oracle Java. Oracle JavaScript interpreter (Graal VM). And ... a pitiful subset of Oracle functionality, filtered through a non-Oracle SQL abstraction layer. It's cute that they're calling it a subset of SQL-92: my bathroom is a subset of my house; I don't actually want to sleep, eat, and hang out in it. The whole thing is outrageous, really, and yet it's also the only way a person can write 

SELECT DISTINCT

. So, here we are. ▪︎ Integer representation of date/datetime values. Good old UNIX timestamp. Still doesn't have a proper SQL function in Oracle SQL (let alone "SuiteQL"), but it's easy enough to compute with basic arithmetic. I prefer to convert the Date/Date time to an integer representation a la UNIX timestamp or JS Date.getTime() value (milliseconds version of UNIX ts) in the query itself. That way, I know that the value being treated is a date value to the query compiler, not a string, and that whatever timezone it is (UTC), I get apples-to-apples treatment if I want to subtract/add an interval, hand it to another script for processing, etc. Example of integer representation of dates LONGINTEGER is not exposed in SuiteQL. Practically speaking that means you can't output a true UNIX second-specific timestamp for recent date values (and obviously no JS Date.getTime() milliseconds-specific integer timestamps). So, if you're dealing with dates and would like an integer timestamp, use integer values as days (like Excel) since 1970-01-01 (like UNIX/*x). Then, scale to seconds or milliseconds for UNIX ts or JS Date as needed. This example queries open invoices of a given project and converts the trandate into an integer value. That value is converted in SuiteScript (JavaScript) into an integer that the Date constructor can understand. Note the use of

TRUNC()

in the query: without that (and the cast to integer), one sees the "under-the-hood" fractional-date value! In other words, you can actually "see" the hours-minutes-seconds of what is supposed to be a simple Date column (trandate). So that thing you thought was an immutable tz-neutral date, that thing you've been using for an entry date on every transaction ever? Yeah. It's not a date. It's a timezone-ignorant datetime. This knowledge is helpful or dangerous, depending on whether you know it. In the words of The Notorious B.I.G., "and if you don't know, now you know."

/**
 * @file Example: how to convert dates to integer values and Date objects using SuiteQL
 * @author ZV
 * @NApiVersion 2.1
 */
define(['N/query'],
/**
* @param {query} nQuery
*/
(nQuery) => {

  /**
   * S[uite]QL to yield Open Invoices of a Project, one row per txn. Use Project Internal ID for bind param.
   * @return {string} S[uite]QL string
   */
  const getProjectOpenInvoicesSql = () => {
    return `SELECT DISTINCT
      project.id AS project_internalid,
      project.entityid AS project_entityid,
      project.altname AS project_name,
      txn.id AS inv_internalid, 
      txn.entity AS inv_customer_internalid,
      cust.entityid AS inv_customer_entityid,
      cust.altname AS inv_customer_name,
      txn.trandisplayname AS inv_displayname,
      txn.tranid AS inv_tranid,
      BUILTIN_RESULT.TYPE_INTEGER(TRUNC(txn.trandate) 
        - TO_DATE('1970-01-01', 'YYYY-MM-DD')) 
        AS inv_trandate_dte_epochdays,
      txn.trandate AS inv_trandate_dte_str,
      txn.foreigntotal AS inv_total
    FROM ((  transaction txn INNER JOIN transactionLine tline ON txn.id = tline.transaction
          ) INNER JOIN entity cust ON txn.entity = cust.id
         ) INNER JOIN job project ON tline.entity = project.id
    WHERE txn.type = 'CustInvc'
      AND tline.entity = ?
      AND txn.foreignamountunpaid > 0
      AND tline.foreignamount <> 0
      AND tline.item IS NOT NULL
    `.replace(/^ {4}/gm, '');
  };

  /**
   * Get Open Invoices of a Project
   * @param {number} projectInternalId
   * @return {{project_internalid: number,  project_entityid: string, project_name: string, inv_internalid: number, inv_customer_internalid: number, inv_customer_entityid: string, inv_customer_name: string, inv_displayname: string, inv_tranid: string, inv_trandate_dte_epochdays: number, inv_trandate_dte_str: string, inv_trandate_dte_unixts: number, inv_trandate_dte_jsts: number, inv_trandate_dte_object: Date, inv_total: number}[]}
   */
  const getProjectOpenInvoices = (projectInternalId) => {
    const query = getProjectOpenInvoicesSql();
    const projectInternalIdSanitized = Number.parseInt(projectInternalId) || 0;
    const params = [projectInternalIdSanitized];
    const rows = nQuery.runSuiteQL({query, params}).asMappedResults();
    // Add row attributes (fields) for UNIX ts, JS ts, and a proper JS Date object.
    rows.forEach(row => {
      const epochdays = row.inv_trandate_dte_epochdays;
      const unixts_epochseconds = epochdays ? epochdays * 24 * 60 * 60 : null;
      const jsts_epochmilliseconds = epochdays ? epochdays * 24 * 60 * 60 * 1000 : null;
      row.inv_trandate_dte_unixts = unixts_epochseconds;
      row.inv_trandate_dte_jsts = jsts_epochmilliseconds;
      row.inv_trandate_dte_object = jsts_epochmilliseconds ? new Date(jsts_epochmilliseconds) : null;
    });
    return rows;
  };

  return {getProjectOpenInvoices};

});

There is a reason i only recommend moment-timezone for date manipulation, you will end up needing its timezone functionality

moment.js is not needed with ECMA6 and later, but if you have it in place and are familiar with it, IMHO it is a very good module that is very widely used for good reason. moment.js will not help one here, however, because SuiteQL produces strings that lack a defined time zone and have a localized formatting determined at runtime. With SuiteQL, it is better to produce something that is guaranteed neutral (from the underlying UTC value, before it becomes a localized string in the output): hence the integer-based representation. Pass that to either moment.js or Date() — either way, the JS handler will be getting input that is TZ neutral and not subject to localized formatting.

suitescript doesnt expose Intl, you will still need an external library for timezone information

@battk Actually, JavaScript Date objects are inherently timezone-aware and localization-neutral. No library is necessary. See, for example,

Date.getTimezoneOffset()

and other methods for dealing with timezones and localization. SuiteScript frequently treats Date objects: record.getValue(), for example, returns a Date object for Date- and DateTime-type fields. That is why I say it would be better if runSuiteQL() and search APIs in general returned Date objects (as opposed to strings). I get the sense from your replies to my posts that what I'm sharing is not your cup of tea. 😂

the Date is not neutral, its methods are relative to a specific timezone

@battk no offense man, but you are very misinformed. The Date object in JavaScript is timezone aware. That means it is timezone neutral. If the data type "knows its own timezone," it's safe -- it's neutral. If the data type is expressed in a localized format that is timezone-specific, and it doesn't tell you it's timezone, it's not safe. This is basic datatypes - programming 101. String is not Date. One of these things knows its timezone.

Your experience sounds very different than mine

Example how to output ISO8601 UTC time from SuiteQL:

SELECT to_char(sys_extract_utc("lastmodifieddate"), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS lastmodifieddate FROM transaction

@Shai Coleman Thank you, that is awesome. 😄 It's a string, true, but I love b/c it is tz-aware (of course, being ISO8601 compliant). Many thanks.