Does DATEADD change the datatype?

I have an odd issue (mainly revolving around proper index usage.)
For some reason when I use a DATEADD function within a join the between or
>= , <= operators the optimizer ignores the index and performs a full table
scan.
Example
This will NOT use the INDEX
--code start
Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
DATEDIFF(MONTH, -1, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0,
GETDATE()))
--code end
Basically the query will return dates today and a 1 month ago. which works
fine but in looking at the query analyzer I find the optimizer is not using
the index where as if i had used a hard coded date or DATEADD(DAY, 0,
DATEDIFF(DAY, 0, GETDATE())) without supplying DATEADD range.
This WILL use the INDEX
Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
DATEDIFF(DAY, 0, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()
))
OR
Select * from Time_Dimension where Date_Number BETWEEN '2/1/2005' AND
'2/28/2005'
OR EVEN.. ( AND THIS ONE IS WEIRD)
Select * from Time_Dimension where Date_Number DATEADD(DAY, 1, DATEDIFF(DAY,
0, GETDATE()))
Any Thoughts
ThanksCheck out what selectivity the optimizer estimates and also if you see an CO
NVERT on the column
side.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3036BF48-0920-4A8C-857D-10337A8C593D@.microsoft.com...
>I have an odd issue (mainly revolving around proper index usage.)
> For some reason when I use a DATEADD function within a join the between or
> scan.
> Example
> This will NOT use the INDEX
> --code start
> Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
> DATEDIFF(MONTH, -1, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0,
> GETDATE()))
> --code end
> Basically the query will return dates today and a 1 month ago. which works
> fine but in looking at the query analyzer I find the optimizer is not usin
g
> the index where as if i had used a hard coded date or DATEADD(DAY, 0,
> DATEDIFF(DAY, 0, GETDATE())) without supplying DATEADD range.
> This WILL use the INDEX
> Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
> DATEDIFF(DAY, 0, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE
()))
> OR
> Select * from Time_Dimension where Date_Number BETWEEN '2/1/2005' AND
> '2/28/2005'
> OR EVEN.. ( AND THIS ONE IS WEIRD)
> Select * from Time_Dimension where Date_Number DATEADD(DAY, 1, DATEDIFF(DA
Y,
> 0, GETDATE()))
> Any Thoughts
> Thanks
>