SQL Server between two datetime fields, not working correctly

Question

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

score 9 · Accepted Answer · edited May 23 '17 at 12:00

9

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.

edited May 23 '17 at 12:00

Community

1
1

answered May 09 '11 at 16:59

gbn

422,506
82
585
676

1

*And pre-SQL Server 2008 yyyymmdd is the only safe date format.*...Unless you use `Set DateFormat`. – Thomas May 09 '11 at 17:01
1

@Thomas: that's why yyyymdd is safe to use: no dependency on Set DateFormat and it's independent of environment. Relying on set DateFormat or set language isn't safe IMHO. What if this is a 3rd party app that has to run on client's SQL boxes?. Did you read the back and to comments in with marc_s in my link? – gbn May 09 '11 at 17:04
DateFormat should always work. In fact, I believe the ADO.NET drivers send Set DateFormat. This issue goes back many versions. I remember reading an article by Kalen Delaney way back in the day of SQL 7.0 on the same topic. If you are writing out the query, then using YYYYMMDD is the safest however often there is an abstraction layer between you and the query. – Thomas May 09 '11 at 17:10
Btw, when I talk about DateFormat always working, I mean you set it before every query that has dates. I do not mean to assume it is set to what you expect before you run your query. – Thomas May 09 '11 at 17:12
1

Bonus for information on indexing, thanks for the valuable information! – Mohgeroth May 09 '11 at 17:46

score 3 · Answer 2 · answered May 09 '11 at 16:57

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.

Matthew · Answer 3 · 2011-05-09T16:57:21.117

2

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

edited May 09 '11 at 16:57

answered May 09 '11 at 16:52

Matthew

10,244
5
49
104

I'm assuming 2005, connection says version 9 but I can do CTE's. 2005 does not have date, only datetime. I beleive date doesn't come into play until 2008 which is why I have the funny conversion to begin with – Mohgeroth May 09 '11 at 16:56
Right, I noticed you mentioned V9... Nonetheless you should not be comparing dates as strings... that's likely the failure here. – Matthew May 09 '11 at 16:56

score 1 · Answer 4 · answered May 09 '11 at 16:52

1

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

answered May 09 '11 at 16:52

Cade Roux

88,164
40
182
265

This works and is the behavaior I was in search of. I was surprised it was working given the conversion to varchar so I was wondering what was going on behind the scenes to let this work in the first place. – Mohgeroth May 09 '11 at 16:58

SQL Server between two datetime fields, not working correctly

4 Answers4

Linked