Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime" - please help

[ansa_naz]

I have the following M code in Query Editor from power BI:

let
    Source = Sql.Database("DB", "DB", [CommandTimeout=#duration(0, 1, 0, 0)]),
    dbo_Calls = Source{[Schema="dbo",Item="Calls"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Calls,{{"Completion Date", type date}}),
    EndDate = Date.From(DateTime.FixedLocalNow()),
    StartDate = Date.AddDays(Date.AddMonths(EndDate,-6),1),
    #"Filtered Rows" = Table.SelectRows(dbo_Calls,each [Completion Date]>=StartDate 
    and [Completion Date]<=EndDate)

in
    #"Filtered Rows"

However this results in the below error:

Expression.Error: We cannot apply operator < to types Date and DateTime.
Details:
    Operator=<
    Left=26/12/2018
    Right=16/05/2017 00:00:00

Any ideas how I can get around this?

Many thanks for all help

[Gordonlilj · Accepted solution]

Hi,

You could either change the datetime column to date or the other way around.

Or you could try adding DateTime.From() to the date columns.

I don't know which one is the datetime columns so i added the function it to all of them.

#"Filtered Rows" = Table.SelectRows(dbo_ANSAPBICalls,each DateTime.From([Completion Date])>=DateTime.From(StartDate) 
    and DateTime.From([Completion Date])<=DateTime.From(EndDate))

[tex628 · Accepted solution]

You are comparing Date format with Datetime format. Change the format from datetime to date on your start and enddate before the filterstatement and it should work fine!

/J

[ebrandt]

I had the same problem, it was caused by a manually added stop-date (used by incremental refresh) with a non exsisting date: 31.06.2023 00:00:00

Because June only has 30 days, I changed it to 30.06.2023 00:00:00

It solved the problem.

[tex628 · Accepted solution]

You are comparing Date format with Datetime format. Change the format from datetime to date on your start and enddate before the filterstatement and it should work fine!

/J

[ansa_naz]

Thank you both

I have amended my M code to below:

EndDate = Date.From(Date.FixedLocalNow()),
StartDate = Date.AddDays(Date.AddMonths(EndDate,-6),1),
#"Filtered Rows" = Table.SelectRows(dbo_Calls,each Date.From([Completion Date])>=Date.From(StartDate) 
and Date.From([Completion Date])<=Date.From(EndDate))

However I now get below error:

Expression.Error: The name 'Date.FixedLocalNow' wasn't recognized.  Make sure it's spelled correctly.

Am I doing something daft here??

[Gordonlilj]

There is no function called Date.FixedLocalNow() which is why you get an error. DateTime.FixedLocalNow() is the name of that function.

[tex628]

I cant really tell whats wrong, can you post the complete query code?

[ansa_naz · Accepted solution]

Its fixed now. I changed all Date functions to DateTime. I also amended the Filtered Rows step so it was using SelectRows from #ChangedType step, not from the original data source:

let
    Source = Sql.Database("DB", "DB", [CommandTimeout=#duration(0, 1, 0, 0)]),
    dbo_Calls = Source{[Schema="dbo",Item="Calls"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Calls,{{"Completion Date", type date}}),
    EndDate = Date.From(DateTime.FixedLocalNow()),
    StartDate = Date.AddDays(Date.AddMonths(EndDate,-6),1),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type",each DateTime.From([Completion Date])>=DateTime.From(StartDate) 
    and DateTime.From([Completion Date])<=DateTime.From(EndDate))
in
    #"Filtered Rows"

Thats done the trick, cheers for all the help!

[Gordonlilj · Accepted solution]

Hi,

You could either change the datetime column to date or the other way around.

Or you could try adding DateTime.From() to the date columns.

I don't know which one is the datetime columns so i added the function it to all of them.

#"Filtered Rows" = Table.SelectRows(dbo_ANSAPBICalls,each DateTime.From([Completion Date])>=DateTime.From(StartDate) 
    and DateTime.From([Completion Date])<=DateTime.From(EndDate))