CODEWITHSUNDEEP
sql-serverbetween
Mohgeroth
Mohgeroth

Reputation: 1627

SQL Server between two datetime fields, not working correctly

SQL Server 2005:

The following 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 given for keyed dates 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

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

The KeyedTimestamp field is DateTime and there are times associated with these records. All records for 3/31/2011 are accounted for. I know I can do this instead by supplying the max time in the second date in between, so I'm not looking for an alternative where clause, but rather an understanding of why it's ignoring the records from the first even though its incorporating the ones from the 31st.

Its almost as if its checking for 3/1/2011 23:59:59, I was hoping I could eliminate this kind of check where I only care about the date, not the time

Upvotes: 2

Views: 33954

Answers (4)

gbn
gbn

Reputation: 432657

Avoid a function on the column by not using BETWEEN. A function mean any index will never be used

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

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

Upvotes: 9

Thomas
Thomas

Reputation: 64674

You shouldn't be converting the datetimes to strings. Instead, compare them as datetimes. It sounds like you are trying to get around the problem of times being stored:

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. I.e., it is inclusive of both end points. In the above solution, I'm getting the first day of the following month and asking for all values strictly less than that value which means all values in the month of March, including times, will be included. Finally, the Select statement is stripping the time value from all return KeyedTimeStamp values.

Upvotes: 3

Matthew
Matthew

Reputation: 10444

Have you tried converting to DATETIME values (or similar) and then comparing? You're comparing strings... I don't know what '03/00/2011' means, conceptually, to the BETWEEN operator. Frankly, I'm surprised that your results make any sense at all!

SQL Server 2008 has a native DATE type, which excludes timestamps. If you do not 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 Anatoly Lubarsky's blog:
http://blogs.x2line.com/al/archive/2006/02/17/1458.aspx

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89741

Datetime compares isn't coming into it, everything 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))

Upvotes: 1

Related Questions