SQL Server between two datetime fields not working correctly

Question

SQL Server 2005:

Next view

SELECT CONVERT(VARCHAR(20), keyedtimestamp, 101) as KeyedDate FROM TMSSTATFILE_STATS a WHERE (CONVERT(VARCHAR(20), a.KeyedTimestamp, 101) BETWEEN '03/01/2011' And '03/31/2011') ORDER BY KeyedDate 

Results are shown for key dates from 3/2/2011 to 3/31/2011.

If I change the first date to 03/00/2011

  SELECT CONVERT(VARCHAR(20), keyedtimestamp, 101) as KeyedDate FROM TMSSTATFILE_STATS a WHERE (CONVERT(VARCHAR(20), a.KeyedTimestamp, 101) BETWEEN '03/00/2011' And '03/31/2011') ORDER BY KeyedDate 

now it gives data for dates from 3/1/2011 to 3/31/2011

The KeyedTimestamp field is a DateTime, and there is time with these entries. All entries for 3/31/2011 taken into account. I know that I can do this instead by setting the maximum time on the second date between them, so I am not looking for an alternative where where, but rather understand why he ignores entries from the first, even if his inclusion is from the 31st.

It’s almost as if he were checking on 3/1/2011 23:59:59, I was hoping that I could eliminate this kind of check, where I only care about the date, not the time

Answer 1

Avoid functions in a column without using BETWEEN. The function means that any index will never be used.

 WHERE a.KeyedTimestamp >= '20110301' AND a.KeyedTimestamp < '20110401' 

And pre-SQL Server 2008 yyyymmdd is the only secure date format.

Answer 2

Do not convert dates to strings. Instead, compare them as time. It looks like you are trying to solve a time storage problem:

 Select DateAdd(d, DateDiff(d, 0, T.KeyedTimeStamp), 0) As KeyedDate From TMSSTATFILE_STATS As T Where T.KeyedTimeStamp >= '20110301' And T.KeyedTimeStamp < DateAdd(m,1,'20110301') 

It should be noted that DateTimeVal Between DateTimeA And DateTimeB translates to DateTimeVal >= DateTimeA And DateTimeVal <= DateTimeB . Ie, it includes both endpoints. In the above solution, I get the first day of the next month and request all values ​​strictly less than this value, which means that all values ​​in March, including time, will be included. Finally, the Select statement removes the time value from all KeyedTimeStamp values.

Answer 3

Have you tried converting DATETIME values ​​(or similar) and then comparing? You are comparing strings ... I do not know what '03/00/2011' means, conceptually, for the BETWEEN operator. Honestly, I am surprised that your results make any sense!

SQL Server 2008 has its own DATE type, which excludes timestamps. If you don't have a native type (you mentioned V9) without a timestamp, you can use something like this:

 SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, your_date_here)) 

As taken from the blog of Anatoly Lyubarsky:
http://blogs.x2line.com/al/archive/2006/02/17/1458.aspx

Answer 4

Comparing Datetime is not included in this; all of this is VARCHAR while you are comparing.

Try:

 SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, keyedtimestamp)) as KeyedDate FROM TMSSTATFILE_STATS a WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, keyedtimestamp)) BETWEEN '03/01/2011' AND '03/31/2011' ORDER BY DATEADD(dd, 0, DATEDIFF(dd, 0, keyedtimestamp))