Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
alau020
Frequent Visitor

Select records using date range by SQL statement

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 databaseData 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

Power BI Data Type.PNG

 

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

 

Many thanks

 

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

View solution in original post

5 REPLIES 5
Phil_Seamark
Microsoft Employee
Microsoft Employee

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

The documentDate column datatype is DateTime

 

Data Typoe in Database.PNG

 

Thank you

So did my suggestion work?

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.