Remember that the supported dateparts and the behavior of the DATEDIFF function can vary depending on the version of SQL Server you're using. Always consult the official Microsoft SQL Server ...

I have a SELECT statement, it is calculating the same DateDiff about 5 times.I'm using some CASE WHEN stuff and the result of DateDiff in days is how I determine what part of the case it goes with ...

This happens because Metabase translates datetimeDiff into the SQL Server function DATEDIFF_BIG, which is not supported in Synapse's serverless SQL pools.