dateadd function works in SQL server but gives error in excel

Question

I have a simple query that returns transaction ids in the last quarter. It works great when I run it on the server (SQL Server 2008 R2), but renders an error when added in Excel using Data > From Other Source > From Microsoft Query.

SELECT
  transact_id,
  trn_date
FROM rimstrn t
WHERE 
  trn_date BETWEEN dateadd(qq, datediff(qq, 0, current_timestamp) - 1, 0) 
      AND dateadd(dd,-1,dateadd(qq, datediff(qq, 0, GETDATE()), 0 )) 

The error received in Excel is shown below:

DateAdd function requires 3 argument(s). Statement could not be prepared

After a lot of trial and error, I realized the query will work in Excel if I change the query to use SELECT TOP 100 ... instead of getting all the records, but the total records are less than 100.

Why do I get this error?

Answer 1

Please try with: Data > From Other Source > From SQL Server