Geek asks and answers

Unplanned CAST Analysis

I have an academic scenario that I would like to know how to analyze.

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

This will round the date to Thursday.

What I have been questioned is evidence in which there are implicit CAST.

These are three places where I assume this should happen ...

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, since 0they 3are constants, is this done at compile time in terms of execution?

But if there 3were INT variables, would that be different?


Is there a way to analyze the implementation plan or some other method to determine this for real?

To complicate the situation, I'm not online right now. I am trying to remotely help a colleague with this. This means that I do not have direct access to SSMS, etc.

+5
1

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

, .

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'))

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)))

, , , int CONVERT_IMPLICIT CONVERT_IMPLICIT int

+7

More articles:


All Articles