Date Add SQL error

[Anonymous]

Hi all,

I'm attempting to connect to a denodo database via SQL statement and I'm running into issue using the Dateadd function.  When using the below statement "DOCPOSTINGDATE BETWEEN DATEADD(Month, -12, CURRENT_DATE) AND CURRENT_DATE" I recieve the following error:

DataSource.Error: ODBC: ERROR [ ] ERROR: Error in select view conditions: Field not found 'month' in view with schema

This SQL statement executes fine in other databases.  Is there some other syntax that power BI expects?

[Anonymous · Accepted solution]

Hi all,

I've solved my issue by consulting the help documentation around the database type my company is using, we're using a denodo solution and they use VQL for querying language which has slightly different syntax.  In the end my query looked as below and now operates as expected:

DOCPOSTINGDATE >= ADDYEAR(CURRENT_DATE, -1)

[amitchandak]

@Anonymous ,

dateadd seems fine, but check  -https://www.w3schools.com/sql/func_sqlserver_dateadd.asp

Try GETDATE() in place of CURRENT_DATE in case of SQL server

[Fowmy]

@Anonymous 

The DATEADD function has the following format, you need to amend your formula accordingly

= DATEADD(DateTime[DateKey],-1,year)

check the article for more info.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn