Datetime2 is not a defined system type

[ryans]

Hello All,

I am having some issues with filtering a column by dates when connecting to SQL Server 2005. Whenever I try to filter by dates in the query editor >= 01/01/2015 I get an error:

DataSource.Error: Microsoft SQL: Type datetime2 is not a defined system type.
Details:
DataSourceKind=SQL
DataSourcePath=1552ts;tmsdata
Message=Type datetime2 is not a defined system type.
Number=243
Class=16

After a brief google search it looks like the culprit is SQL Server 2005 and Power Query possibly don't play nice together with datetime values. Does anyone know a way of filtering these dates and still have the query fold back? Because of the size of the table I am trying to keep it from pulling everything down and then transforming. 

The only other option I see at this time is to send a SQL statement with the query but from my understanding that would prevent the rest of my transformations from folding and while my SQL is decent I have a hard time with joins on multiple tables.

If anyone has any advice please let me know!

[Anonymous]

Do you have any control over the database you're connecting to? If so you could look into adding a view on top of the data source and converting the date into text or an integer (or joining to a date dimension) and filtering on that instead.

[ryans]

Unfortunately, no control. Thats a good idea joining it to a date table then I could filter by the values for year and business period.

[tristanstcyr · Accepted solution]

We fixed this internally this month. You can expect a fix for this in the March release.

[averied]

Was this fix already release?

I've got the same error when filtering a SQL 2005 date on Power Bi desktop version 2.34.4372.322 64-bit (abril de 2016)

[Rasmusrock]

I have also just run into this error. Has it been fixed already? or is there a simple way to solve it?

Thanks in advance,

/Rasmus

[ryans]

@tristanstcyr I am still having this issue. Any update?

[tristanstcyr]

Hi Ryan,

I'm sorry this is still not working for you. Let me loop in the engineer who worked on this...

Tristan

[apbiuser]

I am having the same issue as the user from 2016.

I have a report developed that is attached to a dashboard.  I use a SQL database as the source of my report.  The version is SQL Server Express Edition 9.4.5000.00 and it is the backend of an IT ticketing system.  I am able to pull data, publish and refresh from the Desktop.  I have scheduled the dataset to refresh.  The scheduled refresh will not complete on the scheduler and I cannot make it refresh.

I get Underlying error code -2147467259 Table:TASKS

Underlying error message Microsoft SQL: Type datetime2 is not a defined system type

DM_ErrorDetailNameCode_UnderlyingHResult -2147467259

Microsoft.Data.Mashup.ValueError.Reason DataSource.Error

Cluster URI WABI-US-EAST2-redirect.analysis.windows.net

I have the most current version of desktop loaded.  When I googled the error it looks like something that existed back in 2016 that is supposed to be fixed now. 

I did not see a solution marked for this issue.  Recommendation(s)?

[ryans]

@tristanstcyr That is awesome! Will make my life a lot easier.