Excel user getting conversion error from SQL Server when refreshing data from view

Question

(EDITED - data type and language info added)

I created a view Foo2 for an employee. Whenever he tries to refresh the data, he is getting error:

[DataSource.Error] Microsoft SQL Server: the conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

When I (sysadmin role) try to refresh data in the Excel sheet everything goes smoothly and query in SSMS does not throw any errors. The user also uses another view Foo1 - a previous version of Foo2. That one works as expected.

Right now I am at this point:

• I changed the SELECT statements in Foo1 and Foo2 to be exactly the same
• The views are in the same database and schema
• The employee is accessing the server and database by being a member of a windows login group
• View properties windows are the same except for two extended properties in the Foo1 view - MS_DiagramPane1 and MS_DiagramPaneCount (don't know what they are)

Language and data type related info:

• Collation of the views is the same (SQL_Slovak_CP1250_CI_AS)
• Language of both LOGINs is the same (Slovak - slovenčina)
• Collation of both USERs is the same (SQL_Slovak_CP1250_CI_AS)
• We both have Slovak version of MS 365
• No CAST, CONVERT or other data type changes occour in the view
• Data types in both Foo1 and Foo2 are the same

I suspect, that the user group was given a permission on that particular view Foo1, but I don't know how to test this theory.

• SQL Server 2019 (v15.0.2000.5)
• Excel version: Microsoft 365 for Enterprise

Answer 1

So the problem was the WHERE part of he SELECT statement.

SELECT
    clmn1
    ,clmn2
    ,clmn3
FROM table1
WHERE clmn1 > '2017-12-31'

The column clmn1 is data type smalldatetime. I still don't understand the reason, since implicit conversion from varchar to smalldatetime should be possible. At least according to the chart here:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver15

The SELECT statement works as dasired after using CONVERT in the WHERE clause:

SELECT
    clmn1
    ,clmn2
    ,clmn3
FROM table1
WHERE clmn1 > CONVERT(smalldatetime, '2017-12-31 23:59:59', 120)

I guess I'm missing something. Anyway - problem solved.