Databases
StackOverflow2013
Postsdbo
POT-SQL Between Dates Confusion
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL Between Dates Confusion
    text
    copied!<p>I am working with T-SQL in SQL Server 2000 and I have a table <code>TRANSACTIONS</code> which has a date column <code>TRANDATE</code> defined as DateTime, among many other columns which are irrelevant for this question..</p> <p>The table is populated with transactions spanning many years. I ran into code, test, that has me confused. There is a simple <code>SELECT</code>, like this:</p> <pre><code>SELECT TRANDATE, RECEIPTNUMBER FROM TRANSACTIONS WHERE TRANDATE BETWEEN '12/01/2010' and '12/31/2010' ORDER BY TRANDATE </code></pre> <p>and its not returning two rows of data that I know are in that table. </p> <p>With the statement above, the last row its returning, in order, has a <code>TRANDATE</code> of: 2010-12-31 00:00:00.000</p> <p>When I modify the statement like below, I get the additional two rows for December 2010 that are in that table:</p> <pre><code>SELECT TRANDATE, RECEIPTNUMBER FROM TRANSACTIONS WHERE TRANDATE BETWEEN '12/01/2010 00:00:00' and '12/31/2010 23:59:59' ORDER BY TRANDATE </code></pre> <p>I have tried to find out why the <code>BETWEEN</code> operator doesnt include ALL rows for the 24 period in 12/31/2010 when using the first <code>SELECT</code>, above. And why does it need to have the explicit hours added to the <code>SELECT</code> statement as in the second, modified, statement to get it to pull the correct number of rows out? </p> <p>Is it because of the way <code>TRANDATE</code> is defined as "<code>DATETIME</code>"? </p> <p>Based on this finding, I think that am going to have to go through all of this old code because these <code>BETWEEN</code> operators are littered throughout this old system and it seems like its not pulling all of the data properly. I just wanted clarification from some folks first. Thanks!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload