between not betweening inclusive

In the sql statements below, both BETWEEN and (>= and <=) are giving unexpected results. I am working with monthly data where in one table the time part of [DateTime]=09:31:00 the result is correct but if the time is 16:00:00 (in another table) the statement returns empty. Also if I change either sql statement to use 16:01:00, it returns the second table correctly but that should not be necessary because both statements below are supposed to be inclusive. Anybody know why this is happening and what to do about it?

select distinct * from [clean].[table2] where

convert(varchar, [DateTime],126) between '2005-11-30' and '2005-12-01' and

convert(varchar, [DateTime],114) between '09:30:00' and '16:00:00'

order by [DateTime]

The following statement has the same problem:

select distinct * from [clean].[table2] where

(convert(varchar,[DateTime],126) >= '2006-06-05' and

convert(varchar,[DateTime],126) <= '2006-11-05') and

(convert(varchar,[DateTime],114) >= '09:30:00' and

convert(varchar,[DateTime],114) <= '16:00:00')

order by [DateTime]

Time also has a milliseconds value. It is quite likely that your time values are in actuallity greater than exactly 16:00:00 -perhaps 16:00:00.001.

If you wish to include the times that are between 16:00:00 and 16:00:01 (meaning all times with any millisecond value, then use 16:00:01.

If you wish to ignore the milliseconds value, you may be better served using the smalldatetime datatype.

|||

rwbogosian wrote:

In the sql statements below, both BETWEEN and (>= and <=) are giving unexpected results. I am working with monthly data where in one table the time part of [DateTime]=09:31:00 the result is correct but if the time is 16:00:00 (in another table) the statement returns empty. Also if I change either sql statement to use 16:01:00, it returns the second table correctly but that should not be necessary because both statements below are supposed to be inclusive. Anybody know why this is happening and what to do about it?

select distinct * from [clean].[table2] where

convert(varchar, [DateTime],126) between '2005-11-30' and '2005-12-01' and

convert(varchar, [DateTime],114) between '09:30:00' and '16:00:00'

order by [DateTime]

The following statement has the same problem:

select distinct * from [clean].[table2] where

(convert(varchar,[DateTime],126) >= '2006-06-05' and

convert(varchar,[DateTime],126) <= '2006-11-05') and

(convert(varchar,[DateTime],114) >= '09:30:00' and

convert(varchar,[DateTime],114) <= '16:00:00')

order by [DateTime]

Why cant you just use

SELECT DISTINCT
*
FROM
clean.table2
WHERE
DateTime >= '2006-06-05 09:30:00' AND DateTime <= '2006-11-05 16:00:00'
|||Your query is doing string compares, which never work with between.

You need to do this:

SELECT DISTINCT * FROM [clean].[table2] WHERE
[DateTime] between CAST('2005-11-30' AS DATETIME) AND CAST('2005-12-01' AS DATETIME) AND
CONVERT(DATETIME,CONVERT(varchar,[DateTime],114)) BETWEEN CONVERT(datetime, '09:30:00') AND CONVERT(datetime, '16:00:00')

This will convert the dates to 1900-01-01 and compare the times correctly.

There is no reason to convert the dates to strings.|||

Changing the statement to use 16:00:01 does work. The actual value in the table is 16:00:00.000 but not even 16:00:00.9999 works; it has to be a full second after the actual time in the database table.

I know in the near future I'm going to have problems when we start analyzing even smaller time frames. While I am happy that changing one second works, shouldn't BETWEEN and (<= or >=) be inclusive. Is this a bug or a feature?

|||Can't use your statement because I can't use anything before 09:30:00 or after 16:00:00 on any of the dates between (inclusive).|||Thanks Tom; that appears to be working.