Date comparison problem

Hi

I have a table that contains information that has start dates and end
dates. They are stored in short date format.

I have built a web page that initially returned all the information. I
then want to return information spcific to todays date, where I used

dim todDate
todDate = now

then I did

shTodDate = FormatDateTime(todDate, 2)

which returned the date to a web page that was in the same format as
what was in the access table.

SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"

This was fine beacuse it returned all events that started on that day.
This doesn't help though because some events last a few days, so I
tried the following

SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
endDate<=#'&shTodDate&'#;

This still only returned events that started on the current day.

Does anyone know how I can use <= or >= when comparing dates?

Thanks
puREp3s+puREp3s+ (colin42@.btinternet.com) writes:
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?

Maybe the folks over in comp.datatabases.ms-access knows? It seems
that you are using Access, from the syntax. At least it is not MS
SQL Server.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It looks like you have your comparison operators backwards. In TSQL syntax,
this is what you would do to get any events in progress at this moment:

DECLARE @.t DATETIME
SET @.t = CURRENT_TIMESTAMP

SELECT *
FROM Events
WHERE stDate <= @.t AND endDate >= @.t;

... or, if the end date may be NULL, then:

SELECT *
FROM Events
WHERE stDate <= @.t AND (endDate >= @.t OR endDate IS NULL);

Hope that helps,
Rich

"puREp3s+" <colin42@.btinternet.com> wrote in message
news:7a6f18a3.0310211330.7ad2a1d0@.posting.google.c om...
> Hi
> I have a table that contains information that has start dates and end
> dates. They are stored in short date format.
> I have built a web page that initially returned all the information. I
> then want to return information spcific to todays date, where I used
> dim todDate
> todDate = now
> then I did
> shTodDate = FormatDateTime(todDate, 2)
> which returned the date to a web page that was in the same format as
> what was in the access table.
> SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"
> This was fine beacuse it returned all events that started on that day.
> This doesn't help though because some events last a few days, so I
> tried the following
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?
> Thanks
> puREp3s+