T-SQL between confusion dates

Question

I am working with T-SQL in SQL Server 2000, and I have a TRANSACTIONS table that has a TRANDATE date column defined as DateTime, among many other columns that are not relevant to this issue ..

The table is populated with transactions spanning many years. I came across a code, a test that confused me. There is a simple SELECT , for example:

 SELECT TRANDATE, RECEIPTNUMBER FROM TRANSACTIONS WHERE TRANDATE BETWEEN '12/01/2010' and '12/31/2010' ORDER BY TRANDATE 

and do not return two rows of data that, as I know, are in this table.

With the above statement, the last row returning, in order, has TRANDATE of: 2010-12-31 00: 00: 00.000

When I change the instruction as shown below, I get an additional two lines for December 2010, which are in this table:

 SELECT TRANDATE, RECEIPTNUMBER FROM TRANSACTIONS WHERE TRANDATE BETWEEN '12/01/2010 00:00:00' and '12/31/2010 23:59:59' ORDER BY TRANDATE 

I tried to find out why the BETWEEN statement does not include ALL rows for 24 periods on 12/31/2010 when using the first SELECT above. And why is it necessary for explicit hours to be added to the SELECT , as in the second, modified statement, to make it pull out the correct number of rows?

Is it because of the way TRANDATE is defined as " DATETIME "?

Based on this finding, I think I will have to go through all this old code, because these BETWEEN statements are clogged up in this whole old system, and it looks like it did not pull all the data properly. I just wanted to clarify the situation with some people. Thanks!

Answer 1

A date is a point in time, not a time interval.

'12/31/2010' also the point. Namely, it is at midnight on December 31.
Everything that happened after this moment is ignored. This is exactly the behavior you want (even if you have not realized it yet).

Do not think that when you decide to omit part of the time, it is magically assumed that it is "any" . It will be "all zeroes" , that is, midnight.

Answer 2

As you have discovered, if you do not specify the time for entering the date, by default it is equal to midnight in the morning. So 12/31/2010 stops at midnight when this day begins.

To get all dates for 12/31/2010, you can specify the time how you did it, or add one day to the end date . Without any time, 1/1/2011 ends at midnight 12/31/2010. So you can do BETWEEN 12/1/2010 AND 1/1/2011 . You can use DATEADD to add a day to your SQL if that makes it easier.

In this second approach, a day is added. You will receive any entries for 1/1/2011 that carry the time 00:00:00.

Here is one way to do DATEADD :

 DECLARE @FromDate datetime, @ToDate datetime // These might be stored procedure input parameters SET @FromDate = '12/1/2010' SET @ToDate = '12/31/2010' SET @ToDate = DATEADD(d, 1, @ToDate) 

Then you use @ToDate in your WHERE in the BETWEEN WHERE in the usual way.

Answer 3

'12 / 01/2010 'means '12 / 01/2010 00:00:00' and '12 / 31/2010 'means '12 / 31/2010 00:00:00'. This is why datetime values ​​that fall later on 12/31/2010 are excluded from your query results.

Answer 4

What will be your expected result if I do this

 Insert "12/31/2010" into your datetime column? 

Exactly: 12-31-2010 00:00:00

So why do you expect it to be different as an argument to the request?

Answer 5

You have already answered your question. What you observed is the way SQL Server works.

If you need this confirmation, this MSDN document should say so.

When the undefined part of the time is not specified, the default is 12:00 AM. Note that the line that contains the temporary part is after 12:00 AM in 1998-0105. are not returned by this request because it is out of range.

Edit

As for your comment, datetime is essentially a floating point value.

The following script shows which numbers SQL Server works with.
40541.9749 (12/31/2010 23:23:59) cannot be enabled if your upper bound is 40541 (12/31/2010)

 DECLARE @ADateTime1 DATETIME DECLARE @ADateTime2 DATETIME DECLARE @ADateTime1AsFloat FLOAT DECLARE @ADateTime2AsFloat FLOAT SET @ADateTime1 = '12/31/2010' SET @ADateTime2 = '12/31/2010 23:23:59' SET @ADateTime1AsFloat = CAST(@ADateTime1 AS FLOAT) SET @ADateTime2AsFloat = CAST(@ADateTime2 AS FLOAT) SELECT @ADateTime1AsFloat, @ADateTime2AsFloat