Compare Date (StoredProcedure)

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

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)

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='' />]

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

<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 />

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).

<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 />

<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.

[<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 />

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='' />]

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.