How can I create a LINQ2SQL query that doesn't use MILLISECOND in the call to DATEDIFF?

Question

By default if you compare two dates in a LINQ2SQL query, the resulting SQL will be

DATEDIFF(MILLISECOND, .....)

which requires using BIGINT as well and usually some CONVERT calls depending on what you're doing. As an example, try looking at the SQL output if you write

(DateTime1 - DateTime2).Days

It's a mess!

I would just like to call DATEDIFF(DAY, ...) for example. Is this possible?

Answer 1

It is possible! It turns out there are a few nice methods located in System.Data.Linq.SqlClient that do exactly this.

DateDiffDay
DateDiffMinute
DateDiffMillisecond
.....

The resulting call to DATEDIFF will contain the appropriate first parameter and you can forget about all the calls to CONVERT.