problem with query

Hello group,
I have the following issue:
select convert(varchar, GETDATE(), 105)
--
30-10-2007
(1 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda
--
28-10-2007
30-10-2007
31-10-2007
01-11-2007
(4 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda where
convert(varchar,ini_fecha_actividad,105) between convert(varchar,
GETDATE(), 105) AND convert(varchar, dateadd(day,1,GETDATE()), 105)
--
30-10-2007
31-10-2007
(2 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda where
convert(varchar,ini_fecha_actividad,105) between convert(varchar,
GETDATE(), 105) and convert(varchar, dateadd(day,5,GETDATE()), 105)
--
(0 row(s) affected)
What is the problem with my last query ? It should return:
30-10-2007
31-10-2007
01-11-2007
Any help will be REALLY appreciated.
greetings,
hansHi 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.
--
James Chacata
jameschac@.hotmail.com
"Hans" wrote:
> Hello group,
> I have the following issue:
> select convert(varchar, GETDATE(), 105)
> --
> 30-10-2007
> (1 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda
> --
> 28-10-2007
> 30-10-2007
> 31-10-2007
> 01-11-2007
> (4 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda where
> convert(varchar,ini_fecha_actividad,105) between convert(varchar,
> GETDATE(), 105) AND convert(varchar, dateadd(day,1,GETDATE()), 105)
> --
> 30-10-2007
> 31-10-2007
> (2 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda where
> convert(varchar,ini_fecha_actividad,105) between convert(varchar,
> GETDATE(), 105) and convert(varchar, dateadd(day,5,GETDATE()), 105)
> --
> (0 row(s) affected)
>
> What is the problem with my last query ? It should return:
> 30-10-2007
> 31-10-2007
> 01-11-2007
>
> Any help will be REALLY appreciated.
> greetings,
> hans
>