Datatype datetime2 is mapped to string

[evaverbeke]

Hi,

When processing a table that contains a datetime2(7) column in the source and the DateTime datatype in TE3, I get the following error:

Failed to save modifications to the server. Error returned: 'The following system error occurred: Type mismatch. Unable to convert a value to the data type requested for table 'ARTS' column 'arts - geboortedatum'. The data provider was unable to convert the value '1986-08-28 00:00:00.0000000' from the source data type 'VT_BSTR' to the expected data type 'VT_DATE'. Please check the column data types in the data model and data source and ensure that the data types are compatible.

When I select "Update table schema", I get the suggestion to change the datatype of the column to string

In the source table, the column is datetime2(7):

I'm working in Tabular Editor 3.3.5, the source is SQL Server 2016 SP2 CU12, the model is deployed on a SQL Server Analysis Services 2019.

Thanks in advance for the help!

Best, Eva

[otykier]

Hi Eva, Thanks for reaching out. The AS engine is not able to convert such a value to DateTime (as indicated by the error message). Consider creating a view on your SQL source, where you perform an explicit conversion, or perform the conversion within the partition SQL query in your model, i.e.:

SELECT
    CONVERT(DATETIME, [geboortedatum]) AS [geboortedatum],
    ...
FROM
    ...

[evaverbeke]

Hi Daniel,

Thanks for your reply. The situation is quite odd, it's only since I've last deployed my model, on Monday, that I get this error message. Other models that I haven't deployed recently that use the same table process just fine. In the past, the datetime2(7) datatype also got mapped to a DateTime in Tabular Editor.

Did something change due to which this isn't the case anymore?

Best, Eva

[otykier]

The error message indicates that the source column is indeed a string (VT_BSTR). Are you sure that you're processing the model from the correct table/database? Could it be a mixup between a Dev/Test server?

[evaverbeke]

Hi Daniel,

Yes, I'm sure it's the correct table. I added two columns in the view:

CONVERT(DATETIME, [geboortedatum]) AS [geboortedatum_datetime], CONVERT(DATETIME2(7), [geboortedatum]) AS [geboortedatum_datetime2]

to test what happens, and in SSMS, I can see the datatypes are correct:

But when I refresh table metadata in TE, I get the following:

I just created these columns, only on one server, so I'm sure it's the correct server.

I could change all my datetime2 datatypes to datetime, but I don't understand why datetime2 isn't accepted anymore.

Thanks, Best, Eva

[otykier]

It could also be an issue with the range of values in the column. Just to clarify, is the DataType of the column in your tabular model currently set to DateTime? And are you getting the error when you perform a full processing of the table? What about the other models you mentioned, that use the same table. Do these tables also import the column as DateTime? If so, there should not be any difference in what the result of processing them is.

[evaverbeke]

Hi Daniel,

Yes, the datatype is set to DateTime. I checked the column in the other model, and apparently, there the column is imported as a String, so that explains the different behaviour in the two models.

But I'm still puzzled by why the column is suddenly imported and recognized as a string, while it is in fact a datetime2 column. I experienced the same issue a while back, in TE2, but this turned out to be a bug. Do you have any other suggestions as to why this behaviour might have changed or where I could look to troubleshoot the problem?

Thanks in advance for the help, Best, Eva

[otykier]

Are you able to process the table when the column is imported as DateTime? From your original post it sounded like you got an error in this case. The problem is that a SQL datetime2(7) column could potentially contain values that cannot be interpreted as DateTime in Analysis Services, hence why treating it as a string is a better option.

[evaverbeke]

No, I can't process the table when the column is imported as a DateTime, indeed, that's when I get the error. I just can't figure out why this behaviour changed - up until last week, there was no issue, and datetime2 was getting imported as DateTime just fine, but now this is recognized as a String column.

I've now changed the column to String, so this workaround has fixed the issue, but I don't understand the initial behaviour.

Thanks for the help, Best, Eva

[otykier]

As far as I know, nothing should have changed in either Tabular Editor or Analysis Services. However, one thing that could have changed, is the data you're loading into the column. It is perfectly possible for a SQL datetime2(7) column to contain some values that can be converted to AS DateTime values, while also containing other values that cannot be converted to AS DateTime values, giving you an error message similar to what you mentioned in the initial post. You could easily test this, for example by loading just a subset of rows into your tabular model, or by checking if the column contains any values that fall outside the valid range of AS DateTime values.