Hi Hans,

The problem is that in your WHERE clause you are comparing the dates as
character strings. As such the end period in your BETWEEN becomes
'05-11-2007'. That sorts alphabetically before any of the other dates and
your range does not return any results.

One of your queries returns the desired results but that is just by
coincidence (because in that case the character representation of the end
date is higher in order).

The correct way to return a range of dates is to compare dates in their
native format, not to convert them. To trim the time portion you can use
different techniques, one is via using the DATEADD and DATEDIFF functions.

Here is a query that will return the desired results:

SELECT CONVERT(VARCHAR, ini_fecha_actividad, 105)
FROM Agenda
WHERE ini_fecha_actividad >= DATEADD(day, DATEDIFF(day, '20010101',
CURRENT_TIMESTAMP), '20010101')
   AND ini_fecha_actividad < DATEADD(day, DATEDIFF(day, '20010101',
CURRENT_TIMESTAMP) + 6, '20010101');

Note that I changed the math and added 6 days to the upper boundary to
include the results dates for the next 5 days.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Hans, you should try this approach which compares dates with datetypes.

declare @d1 smalldatetime
set @d1 = '11-01-2007'  -- Nov 1st
select convert(varchar, @d1, 105)
where @d1 between GETDATE() and dateadd(day,5,GETDATE())

answer: 01-11-2007

I removed the references to a table but you get the format - works the same.