SQL NetSuite Connector DATEADD Function

Sweep_The_Leg
Sweep_The_Leg Member
in Archive

Trying to do a simple DATEADD to offset the UTC time change on our date fields. I've tried every which way to format this formula, but nothing works. Just keep getting the below error.  Any ideas?

 

SELECT
TRANDATE,
DATEADD(day,1,TRANDATE) as 'date adj',
TRANID

FROM TRANSACTIONS

 

NetSuite][SuiteAnalytics Connect JDBC Driver][OpenAccess SDK SQL Engine]Failed to retrieve data. Error ticket#...

Comments

  • DDalt
    DDalt Member

    I get the sense that only certain SQL commands are allowed in the Query section when creating a data set from SuiteAnalytics Connect. For example, I tried running this query:

     

    SELECT TRANDATE, TRANID FROM TRANSACTIONS LIMIT 100 

     

    and recieved the same error message for the multiple DATE_ADD functions I tried. Perhaps someone from Domo can confirm these thoughts? I could not find any documentation on what kinds of SQL commands are permitted when a query helper is built into a connector.

     

    If you can't use the DATE_ADD function directly in the query section, as it appears we can't at the moment, then you will have to utilize a data flow to transform the raw data. 

  • DDalt
    DDalt Member

    UPDATE:

     

    It appears that you have to use Oracle-specific SQL language in this connector whenever writing queries. My LIMIT example was rejected because, as I have since discovered, it is not a command that functions in Oracle.

     

    Knowing that we need to stick with Oracle language, this query should work for the original question:

     

    SELECT
    TRANDATE,
    TRANDATE + 1 'new column',
    TRANID

    FROM TRANSACTIONS

     

    Note: there is no "AS" in Oracle SQL language when re-naming columns

     

    Here are the other examples of adding various date components. I suspect you could replace '+' with '-' to subtract:

     

    SELECT 

    TRANID,

    TRANDATE,

    TRANDATE + 1 'PLUS_1_DAY',

    TRANDATE + 1/24 'PLUS_1_HOUR',

    TRANDATE + 1/24/60 'PLUS_1_MINUTE',

    TRANDATE + 1/24/60/60 'PLUS_1_SECOND'

     

    FROM TRANSACTIONS

     

    I have attached a picture of the output of the second query

     

Categories