October 3, 2003 at 9:24 am
We've been getting unexpected results when using the "Between" statement in our queries/stored procedures.
If we run a query selecting records where a date is between '8/1/2003' and 8/31/2003', we will get records for 8/1/2003 through 8/30/2003. No records with a date of 8/31/2003 will be returned.
Put another way, the first date in the between statement appears to be inclusive, while the second date appears to be exclusive.
Same thing appears to happen when using >= and <=. The first date is inclusive, the second is not.
What can you tell me about it? Is the handling of the Between statement liable to change with different versions of whatever software affects this?
Arthur Lorenzini
Arthur Lorenzini
October 3, 2003 at 9:43 am
I guess this has to do with converting the characters to datetime variable. You may need to do an explicit conversion. If you show your code comeone may be able to locate the source of the error. Generally when using character to date conversion, I provide an explicit conversion.
Joseph
October 3, 2003 at 9:46 am
If you don't specify a time, it defaults to 12:00:00.
I suspect your entries for 8/31/2003 will have a time later than 12:00:00.
Change your query to between '8/1/2003 00:00:00' and 8/31/2003 23:59:59'
October 3, 2003 at 9:55 am
the BETWEEN clause is, unfortunately, exactly what the word means. When used with datetime data '8/1/2003' is interpreted as 'August 1, 2003 00:00:00.000' and '8/31/2003' is interpreted as 'August 31, 2003 00:00:00.000'... NOT 'August 31, 2003 23:59:59.997'. So, unless something was created at exactly 12AM on the 31st, you won't get a result. >= and <= are functionally equivalent to BETWEEN.
If you're looking for day-specific data, one alternative is to use the DateDiff function. Your WHERE clause might look like this:
WHERE DateDiff(dd,'8/1/2003',DateColumn) >=0 AND DateDiff(dd,DateColumn,'8/31/2003') <=0
You could, of course, substitute variables.
HTH,
--SJTerrill
October 3, 2003 at 9:58 am
You guys are great. By extending the date out to '8/31/2003 23:59:59' in worked fine.
Thanks a lot.
Arthur Lorenzini
Arthur Lorenzini
October 3, 2003 at 10:19 am
Ianscarlett,
Actually, not using a time defaults to 12:00:00 AM. Which in 24hour format is 00:00:00. So there won't be any entries from 8/31 since the query would default to:
BETWEEN '08/01/2003 00:00:00' AND '08/31/2003 00:00:00'
-SQLBill
October 5, 2003 at 9:46 pm
The following works too:
convert(char(8),tablename.datetimefield,112) between '20030801' and '20030831'
October 6, 2003 at 12:12 am
be carefull with datetime and smalldatetime, the later needs only 23:59 because it will include the next day. Read the definitions of datetime fields.
October 6, 2003 at 6:24 am
If there will ever be an index on your date column, you're better off not using BETWEEN. As you've got the surrounding dates as just dates with no times, I suggest just something like this:
WHERE DateCol >= '20030801' AND DateCol < DATEADD(d,1,'20030831')
If the date range parameters are local parameters of a temporal data type, again with no time components, then just:
WHERE DateCol >= @StartDate AND DateCol < @EndDate + 1
If the local parameters my have a time component (e.g., you're deriving them from GETDATE()), then something like this:
WHERE DateCol >= CONVERT(char(8),@StartDate,112) AND DateCol < CONVERT(char(8),@EndDate + 1,112)
The idea is to not use a function on the column's values in the predicate, which would prevent index seeks.
--Jonathan
Edited by - jonathan on 10/06/2003 06:24:50 AM
--Jonathan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply