SQL NetSuite Connector DATEADD Function
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
-
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.
0 -
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',
TRANIDFROM 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
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 96 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 705 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive