Date versus DateTime functions

[djj55]

Hello, SQL2008R2
My database has a column of type SmallDateTime but no time value is ever stored. ('2011-04-19 00:00:00')

If I have criteria that uses

--Yes I know this is incorrect, that is why I am asking the question
WHERE myColumnDate = DATEADD(day, -10, GETDATE())

My question is it better to have

WHERE myColumnDate = CAST(DATEADD(day, -10, GETDATE()) AS DATE) 
--or
WHERE myColumnDate = DATEADD(dd, DATEDIFF(dd, 0, DATEADD(day, -10, GETDATE())), 0)

or is there a better way?

Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

There is a better way.

Since myColumnDate is a small date time, your formula should return that data type so that SQL doesn't need to do a bunch of needless data type conversions. If this were me, I would do it like this:

Declare @MySmallDateTimeVar SmallDateTime
Set @MySmallDateTimeVar = DATEADD(dd, -10, DATEDIFF(day, 0, GETDATE()))

Select Columns
From   Tables
Where  myColumnDate = @MySmallDateTimeVar

This way, you are guaranteed that the formula is only evaluated once, and since it has the same data type as the column, there won't be any data type conversions occurring for each value in the table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thank you,

That makes sense. As always you explain well.

I am trying to clean up old code and have found several places where it is just plain wrong (see above). So while correcting I am trying to make the code more efficient.

I hate using variables as debugging is a pain. But I plan to use it here.

Thanks,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!