SQL NetSuite Connector DATEADD Function

Sweep_The_Leg

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#...

DDalt

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

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