October 8, 2014 at 12:18 pm
Hi,
I am working in Sqlserver 2008 R2. I would like to know the pros and cons of using Dateddiff
DATEDIFF(dd,getdate(),ExpiryDate) >=0
datepart(dd,ExpiryDate)>= datepart(dd,GETDATE())
cast(ExpiryDate as date)>= cast(getdate() as date)
Which is the best way on the above mentioned samples. please guide me the pros and cons of using dateddiff. also will casting the date to remove the time impact the performance.
October 8, 2014 at 12:34 pm
I don't think out of the three, datepart one wouldn't suffice your requirement. What if the expiry date is of a different month or year? but you are only considering the day there which might give you bad data. You can just simply use DAY(Getdate()) to get same data as that.
I would use Datediff instead of the cast because I don't want to convert the date again and again. Instead I will just check if the difference of the dates is greater than or equal to 0 or not.
Mark as answer, if this has helped you.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
October 8, 2014 at 12:35 pm
DATEDIFF will avoid any usage of indexes on ExpiryDate column.
DATEPART(dd, date) will return wrong results because you're missing month and year.
That cast might allow using indexes if the column is some kind of datetime data type. I wouldn't use it on the column as it makes no sense.
October 8, 2014 at 12:44 pm
a4apple (10/8/2014)
I would use Datediff instead of the cast because I don't want to convert the date again and again. Instead I will just check if the difference of the dates is greater than or equal to 0 or not.
I wouldn't because it will render any index useless.
a4apple (10/8/2014)
Mark as answer, if this has helped you.
Do you really need the approval?
October 8, 2014 at 12:57 pm
I wouldn't because it will render any index useless.
Out of the three options he had, I thought that would be okay to use.
Do you really need the approval?
I said that because, it would help other's with same kind of issue somewhere, sometime. 🙂
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
October 8, 2014 at 1:24 pm
Hi a4apple and Luis,
thanks for your reply. if all of these three is not reliable, which will be best syntax for this requirement. please suggest me
October 8, 2014 at 1:42 pm
Assuming ExpiryDate is a date/time data type column, I would use the following.
ExpiryDate >= DATEADD(dd, DATEDIFF( dd, 0, getdate()), 0)
It's basically the same as
ExpiryDate >= cast(getdate() as date)
But the first option is slightly faster.
You can find more formulas in here:
October 8, 2014 at 1:55 pm
Hi Luis,
Thank you so much for your reply. As you said, i would go with DateAdd. Jut for my curiosity, will casting the date affect the performance?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply