cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryans Regular Visitor
Regular Visitor

Datetime2 is not a defined system type

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!

1 ACCEPTED SOLUTION

Accepted Solutions
tristanstcyr Regular Visitor
Regular Visitor

Re: Datetime2 is not a defined system type

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

View solution in original post

9 REPLIES 9
BarneyL Regular Visitor
Regular Visitor

Re: Datetime2 is not a defined system type

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 Regular Visitor
Regular Visitor

Re: Datetime2 is not a defined system type

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 Regular Visitor
Regular Visitor

Re: Datetime2 is not a defined system type

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

View solution in original post

ryans Regular Visitor
Regular Visitor

Re: Datetime2 is not a defined system type

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

averied Occasional Visitor
Occasional Visitor

Re: Datetime2 is not a defined system type

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 Regular Visitor
Regular Visitor

Re: Datetime2 is not a defined system type

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 Regular Visitor
Regular Visitor

Re: Datetime2 is not a defined system type

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

tristanstcyr Regular Visitor
Regular Visitor

Re: Datetime2 is not a defined system type

Hi Ryan,

 

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

 

Tristan

apbiuser Regular Visitor
Regular Visitor

Re: Datetime2 is not a defined system type

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)?

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,613)