SQL Server Performance

Compare Date (StoredProcedure)

Discussion in 'General Developer Questions' started by mrdotnetid, Oct 12, 2006.

  1. mrdotnetid New Member

    Hello,

    I have problem with my application, I wan't to create Report for daily order activity.
    How to compare 2 date with store procedure in sql server there's
    @mindate(datetime) 'Minimum Order Date With @maxdate(datetime) 'Maximun Order date.

    Thx
  2. FrankKalis Moderator

    Hope that goes your direction. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE PROCEDURE dbo.MyDate (@mindate DATETIME, @maxdate DATETIME)<br />AS <br /> SELECT whatever<br /> FROM Yourtable<br /> WHERE OrderDate &gt;= @mindate AND OrderDate &lt;= @maxdate<br /> GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, OrderDate), 0)<br /> RETURN 0<br /> GO<br /></font id="code"></pre id="code"><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  3. Adriaan New Member

    For date ranges, you can use the BETWEEN operator:<br /><br />WHERE &lt;date_column&gt; BETWEEN @mindate AND @maxdate<br /><br />But as you're looking for today's activities, I'm assuming the date column also records the time of day. In that case you have to eliminate the timepart:<br /><br />WHERE CONVERT(varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, &lt;date_column&gt;, 112) = CONVERT(varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, GETDATE(), 112)
  4. mrdotnetid New Member

    I tried this transact but I have an Error like this :<br /><br />Column 'C.OrderDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.<br /><br />I Don't know what's the mean,sorry because I'm new with SqlServer.<br /><br />Thx For your answer before.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  5. Adriaan New Member

    Frank, that is a weird query. Where did the GROUP BY come from?
  6. Madhivanan Moderator

    Should it be ORDER by? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />For date ranges, you can use the BETWEEN operator:<br /><br />WHERE &lt;date_column&gt; BETWEEN @mindate AND @maxdate<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />For datetime columns, &lt;, =&gt; is preferred over BETWEEN.<br /><br />Here's why :<a target="_blank" href=http://databases.aspfaq.com/general/should-i-use-between-in-my-database-queries.html>http://databases.aspfaq.com/general/should-i-use-between-in-my-database-queries.html</a><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />But as you're looking for today's activities, I'm assuming the date column also records the time of day. In that case you have to eliminate the timepart:<br /><br />WHERE CONVERT(varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, &lt;date_column&gt;, 112) = CONVERT(varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, GETDATE(), 112)<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Applying a function on the &lt;date_column&gt; will eliminate the chances of using an index on this column, ehich is not desirable.<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  8. Adriaan New Member

    I just did a test ...<br /><br />Created a test table with a nonclustered index on datetime column, with 50% of rows having the time of day, the other 50% only the date. Table has 6000 rows, dates are spread fairly evenly.<br /><br />I do get an index scan for CONVERT(VARCHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, &lt;date_column&gt;, 112).
  9. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />I just did a test ...<br />I do get an index scan for CONVERT(VARCHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, &lt;date_column&gt;, 112).<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Remove the convert and you will get an index seek.<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  10. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />I just did a test ...<br />I do get an index scan for CONVERT(VARCHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, &lt;date_column&gt;, 112).<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Remove the convert and you will get an index seek.<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">True, but you said that "Applying a function on the &lt;date_column&gt; will eliminate the chances of using an index on this column, ehich is not desirable.."<br /><br />My comments are usually the same, but an index scan is still better than a table scan.
  11. Roji. P. Thomas New Member

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  12. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Frank, that is a weird query. Where did the GROUP BY come from?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Wel, the original poster was asking for daily order activity. That's why I included the GROUP BY. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  13. Adriaan New Member

    Frank,<br /><br />The weirdness was <b>SELECT whatever FROM Yourtable</b> with a GROUP BY. You should know better than not to supply a proper column list in a GROUP BY query.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  14. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  15. mmarovic Active Member

    I don't know Frank why are you ashamed of refering your own article. The article is very good and it is the best guide I found so far for that kind of questions.
  16. FrankKalis Moderator

    quote:Originally posted by mmarovic

    I don't know Frank why are you ashamed of refering your own article. The article is very good and it is the best guide I found so far for that kind of questions.
    Kind words, Mirko. [:I] Still it feels strange.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de

Share This Page