We have two different projects that connect to two unrelated server/db/table structures and one of them (our heavier use project) converts date ranges to datetime2 in the generated SQL. Another project converts the same style date range filter to just datetime.
After a lot of digging with the looker team, we identified a query return time issue with the datetime2 instance was due to a precedence issue in which the looker convert side of the argument (datetime2) had a higher precedence than the field comparing it to in our table (datetime) and was causing our index to scan instead of seek. We have remedied this with a workaround that strips the 2 from datetime2 in liquid SQL to allow us to get improved performance.
While I am not sure why the convert is not generated to match whatever the field type in the database is, the problem does not replicate itself on a different project/server. On that second server, the convert (in multiple tests on different DBs/tables) is to datetime, allowing the precedence to match the DB field type and use the index efficiently.
The only difference we can note is that the servers are running slightly different SQL versions (13.04446 vs 13.04411), but other than that, don’t know what might cause this difference in convert datatype.