Date between (an easy one ;-))

Hi to all,

What is the best way to check if a dateTime is between two other dateTimes (including the first one, excluding the last one), without taking into account hh,mm,ss and smaller?

Thanks,

Nele

u can use the between operator to do this..it takes both start and in into consideration, but to exclude the last date just reduce a day from that...else u can always use > and <= for comparison...to get just date part of it , use the conversion...an example

declare @.date as datetime

set @.date = getdate()

select convert(varchar(10),@.date,112) as dates where @.date between getdate() and (getdate()+2)

|||If you do it like that, how is the search process in the database then? I can have 10000000 records, if it needs to consult them all, the performance will drastically decrease, I think?|||

-- --
-- This is assuming that we are talking about including 'entire days' -- despite
-- whatever the arguments are
-- --
set nocount on
declare @.dTExamples table (rid integer, testDT datetime)
insert into @.dTExamples values (1, '12/1/2006')
insert into @.dtExamples values (2, '12/1/2006 8:00')
insert into @.dtExamples values (3, '12/4/2006 13:05')
insert into @.dtExamples values (4, '12/7/2006')
insert into @.dtExamples values (5, '12/7/2006 00:00:00.004') -- Rounds down to .003
insert into @.dtExamples values (6, '12/7/2006 00:00:00.005') -- Rounds up to .007
insert into @.dtExamples values (7, '12/7/2006 00:00:00.006') -- Rounds up to .007
insert into @.dtExamples values (8, '12/7/2006 00:00:00.010')
insert into @.dtExamples values (9, '12/7/2006 23:59:59.997')
insert into @.dtExamples values (10,'12/7/2006 23:59:59.999') -- Rounds up to 12/8
insert into @.dtExamples values (10,'12/8/2006 00:00:00.002') -- Rounds up
--select * from @.dtExamples

declare @.loDt datetime set @.loDt = '12/1/2006 07:00:00.000'
declare @.hiDt datetime set @.hiDt = '12/7/2006 00:00:00.006'

-- --
-- The potential problem of the "between" operator is that it is an "inclusive"
-- operator -- that is, it includes values that equal the endpoints. This can
-- be avoided by using the explicit inequality operators.
--
-- Note in particular that RID 10 is not included by this select
--
-- Also note that no operators are applied to the testDt column so that
-- use of an index is not inhibited.
-- --
select * from @.dtExamples
where testDT >= convert (datetime, convert (varchar(10), @.loDt, 112))
and testDt < convert (datetime, convert (varchar(10), @.hiDt, 112)) + 1

-- rid testDT
-- --
-- 1 2006-12-01 00:00:00.000
-- 2 2006-12-01 08:00:00.000
-- 3 2006-12-04 13:05:00.000
-- 4 2006-12-07 00:00:00.000
-- 5 2006-12-07 00:00:00.003
-- 6 2006-12-07 00:00:00.007
-- 7 2006-12-07 00:00:00.007
-- 8 2006-12-07 00:00:00.010
-- 9 2006-12-07 23:59:59.997

-- --
-- Note in particular that RID 10 is included by this select
-- --
select * from @.dtExamples
where testDT between convert (datetime, convert (varchar(10), @.loDt, 112))
and convert (datetime, convert (varchar(10), @.hiDt, 112)) + 1

-- rid testDT
-- --
-- 1 2006-12-01 00:00:00.000
-- 2 2006-12-01 08:00:00.000
-- ...
-- 9 2006-12-07 23:59:59.997
-- 10 2006-12-08 00:00:00.000