Analyising Implict CAST

Question

I have an academic scenario, which I would like to know how to analyse.

DECLARE @date DATETIME
SET     @date = getDate()
SET     @date = DATEADD(DAY, DATEDIFF(DAY, 0, @date-3), 3)

This will round the date down to a Thursday.

What I have been challenged on is to evidence where there are implicit CASTs.

The are three places where I presume that this must be occuring...

DATEADD(
  DAY,
  DATEDIFF(
    DAY,
    0,          -- Implicitly CAST to a DATETIME?
    @date-3     -- I presume the `3` is being implicitly cast to a DATETIME?
  ),
  3             -- Another implicit CAST to a DATETIME?
)

Perhaps, however, as the 0 and 3's are are constants, this is done during compilation to an execution plan?

But if the 3's were INT variables, would that be different?

Is there a way to analyse an execution plan, or some other method, to be able to determine this imperically?

To make matters more complicated, I'm currently off site. I'm trying to remotely assist a colleague with this. Which means I do not have direct access to SSMS, etc.

Answer 1

For the queries

DECLARE @date DATETIME  = getDate()
DECLARE @N INT = 3

SELECT  DATEADD(DAY, DATEDIFF(DAY, 0, @date-3), 3)
FROM master..spt_values

SELECT  DATEADD(DAY, DATEDIFF(DAY, 0, @date-@N), @N)
FROM master..spt_values

And looking at the execution plans the compute scalars show the following.

Query 1

[Expr1003] = Scalar Operator(dateadd(day,datediff(day,'1900-01-01 00:00:00.000',[@date]-'1900-01-04 00:00:00.000'),'1900-01-04 00:00:00.000'))

Query 2

[Expr1003] = Scalar Operator(dateadd(day,datediff(day,'1900-01-01 00:00:00.000',[@date]-CONVERT_IMPLICIT(datetime,[@N],0)),CONVERT_IMPLICIT(datetime,[@N],0)))

showing that your suspicion is correct that it happens at compile time for the literal values but needs a CONVERT_IMPLICIT at run time for the int variables