Select records using date range by SQL statement

[alau020]

Hello,

I am trying to get records from a database (ODBC connection) where the documentDate is greater than 1/04/2017. 

E.G i have entered this SQL statement...

SELECT Amount, quantity, documentDate FROM SalesLineItem
WHERE documentDate > '1/04/2017'

Data Type from the database

I keep getting errors, for example......  Invalid combination of types compared: DATE and VARCHAR" or Invalid combination of types compared: DATE and BIGINT" if i have the date format as 1/04/2017

This is the format of documentDate in power bi

Any thoughts on how to fix this? have I go the wrong format after the WHERE statement?

Many thanks

[Phil_Seamark]

It might be safer to format your date a different way.

eg.

SELECT Amount, quantity, documentDate FROM SalesLineItem
WHERE documentDate > '2017-04-01'

What is the datatype of the documentDate column in the SalesLineItem table in the database?

[alau020]

The documentDate column datatype is DateTime

Thank you

[Phil_Seamark]

So did my suggestion work?

[alau020 · Accepted solution]

The data type was a timestamp and the solution that worked for me is as follws.

SELECT Amount, quantity, documentDate FROM SalesLineItem
WHERE documentDate > {ts '01/04/2017'}

 Thank you for your input.

[alau020]

No, the suggestion did not work. I still get the same errors!!!!