SQL Server Performance Forum – Threads Archive

Queries using DateAdd

I have two questions regarding the DateAdd usage in the following sample query: select
ID,
DateAdd(n,-480,t.TranDate) as TranDate,
TranDescription
from
[TRANS]
where
ID >= 100 and ID <= 999
and TranDate >= {d ‘2005-11-06’}
and TranDate < {d ‘2005-12-06’}
order by
TranDate,
TranDescription 1) Does the TranDate used in the where and order by clauses use the original unmodified TranDate as stored in the database or does it use the DateAdd result from the select clause? 2) Assuming that the where and order by clauses are using the unmodified TranDate stored in the database (which I suspect), what is the optimum way to adjust the query to, at minimum, use the DateAdd result in the where clause (order by original TranDate or modified TranDate will net the same result) — doing the DateAdd calculation twice seems inefficient to me? Any help would be appreciated. Steve Sommers
www.shift4.com

Hi Steve,
the trandate in select and where clause is same based on data from databse.
Finally ordering is done based on the resultset.
So the trandate in order by clause is not the same in db whereas it is populated from final select i.e. the resultset.
So you dont need to use dateadd even in where clause or in order by clause.

Is this SQL Server related question? Madhivanan Failing to plan is Planning to fail

quote:Originally posted by ranjitjain Hi Steve,
the trandate in select and where clause is same based on data from databse.
Finally ordering is done based on the resultset.
So the trandate in order by clause is not the same in db whereas it is populated from final select i.e. the resultset.
So you dont need to use dateadd even in where clause or in order by clause.

Thanks Ranjitjain, Just to clarify, you’re saying that the "TranDate" used in the where clause will, based on this example, be adjusted 480 minutes prior to the comparison? If so, this pleasantly surprises me! Thanks.

quote:Originally posted by Madhivanan Is this SQL Server related question? Madhivanan Failing to plan is Planning to fail

Hi Madhivanan, I think so. I am using SQL Server 2000, my question is T-SQL performance related and I’m a developer. Is this not the correct forum topic?

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Shift4SMS 1) Does the TranDate used in the where and order by clauses use the original unmodified TranDate as stored in the database or does it use the DateAdd result from the select clause? <hr height="1" noshade id="quote"></blockquote id="quote"> The TranDate in the WHERE clause is the unmodified column in your table. Since you can specify an alias in the ORDER BY I would suspect that TranDate in the ORDER BY uses the alias, e.g. your DATEADD() modified data. But in the end, that’s mood, since you add or subtract a constant value, the result is the same either way. No matter if you use the alias or the original column. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> 2) Assuming that the where and order by clauses are using the unmodified TranDate stored in the database (which I suspect), what is the optimum way to adjust the query to, at minimum, use the DateAdd result in the where clause (order by original TranDate or modified TranDate will net the same result) — doing the DateAdd calculation twice seems inefficient to me? <hr height="1" noshade id="quote"></blockquote id="quote"> Correct me, if I’m wrong, but I don’t see that your query uses DATEADD in the WHERE clause. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis The TranDate in the WHERE clause is the unmodified column in your table. Since you can specify an alias in the ORDER BY I would suspect that TranDate in the ORDER BY uses the alias, e.g. your DATEADD() modified data. But in the end, that’s mood, since you add or subtract a constant value, the result is the same either way. No matter if you use the alias or the original column.<hr height="1" noshade id="quote"></blockquote id="quote"> I did some more testing and discovered this same result via the same means — using an alias. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Correct me, if I’m wrong, but I don’t see that your query uses DATEADD in the WHERE clause. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] The fact that you have to specify DATEADD in the ORDER BY and WHERE doesn’t necessarily mean, SQL Server will do the calculate twice. It will apply the DATEADD from the WHERE clause first and than most likely reuse those intermediate results to construct the ORDER BY. Since ORDER BY is a cursor under the covers, SQL Server will build an internal working table anyway and simply sort this by the column you’ve specified.<hr height="1" noshade id="quote"></blockquote id="quote"> You’re right, my example did not show this but this was the basis of my question. The ORDER BY portion I’m not concerned with because TranDate or (TranDate + whatever) will result in the same order. For the WHERE clause, I’m currently leaning towards modifying the value being compared: <ul>"DateAdd(n,-480,t.TranDate) as TranDate" in the SELECT clause and "TranDate >= DateAdd(n,480,{d ‘2005-12-06’}) and TranDate < DateAdd(n,480,{d ‘2005-12-06’})" in the WHERE clause.</ul> (syntactically, I’m not certain this example is correct, having the {d ‘2005-12-06’} as a DataAdd parameter, but hopefully it gets the point across — I do have a version that works correctly) Thanks all for your feedback! Steve Sommers www.shift4.com

]]>