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+

re: Date comparison problem

puREp3s+ (colin42@btinternet.com) writes:[color=blue]
> 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?[/color]

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/techinf...2000/books.asp

re: Date comparison problem

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...[color=blue]
> 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+[/color]