is this a date issue??

[Alistair]

hi all,

I have posted this to inetserver.asp.general too..I thought I had
crossposted but somehow I didn't, so please accept my apologies if the
multipost offends.

ok..the question....

I have a calendar app that is driving me nuts.

you can see it at [url]www.corruption.co.uk/events/calendar.asp[/url]

now, the problem is this...for some reason..only events that occur in
february are showing up

The DB (access 2000) contains 4 entries, each of these is identical except
for the event name and the event date. They occur on 27 Jan, 27 Feb, 27
March and 27 April.

I have response.written the sql so you can see what is being queried.

now, you can add new events if you like to test, but you will discover that
only events in february are displayed....you can confirm that the events are
in the DB by using the "edit my events". this displays what events you have
entered. by doing this you will see that the events have indeed been added
but are only displayed if they occur in february, and it doesn't mater what
year either...events in feb of any year will be displayed..events for any
other month in any year aren't.

I cannot see how this could be a date issue (mm/dd or dd/mm) as surely this
would affect all entries and not just the ones in feb. but if somone could
point out anything I would be really grateful.

source code is available if anyone wants to see it

many thanks

[Aaron Bertrand - MVP]

>>>>> #01/01/04# AND endDate <= #31/01/04#))

HEY!!!!! STOP USING AMBIGUOUS DATE FORMATS!!!!!!!!!!!!

#2004-01-01# AND endDate <= #2004-01-31#

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Invotion]

Hello,

Could it be that access does not support '<=' and '>='
for date/time values? When I try to do the same from
within Access, those operators are not available and I
get a type conversion error if I type them in and execute
the query. Try using > and < instead just to see if it
works. Then you may have to check time values as well to
get the border dates. (i.e. > '12/01/2003 23:59:59' for
january first).

Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
[url]http://www.Invotion.com[/url]

>-----Original Message-----
>hi all,
>
>I have posted this to inetserver.asp.general too..I

thought I had

>crossposted but somehow I didn't, so please accept my

apologies if the

>multipost offends.
>
>ok..the question....
>
>I have a calendar app that is driving me nuts.
>
>you can see it at

[url]www.corruption.co.uk/events/calendar.asp[/url]

>
>now, the problem is this...for some reason..only events

that occur in

>february are showing up
>
>The DB (access 2000) contains 4 entries, each of these

is identical except

>for the event name and the event date. They occur on 27

Jan, 27 Feb, 27

>March and 27 April.
>
>I have response.written the sql so you can see what is

being queried.

>
>now, you can add new events if you like to test, but you

will discover that

>only events in february are displayed....you can confirm

that the events are

>in the DB by using the "edit my events". this displays

what events you have

>entered. by doing this you will see that the events have

indeed been added

>but are only displayed if they occur in february, and it

doesn't mater what

>year either...events in feb of any year will be

displayed..events for any

>other month in any year aren't.
>
>I cannot see how this could be a date issue (mm/dd or

dd/mm) as surely this

>would affect all entries and not just the ones in feb.

but if somone could

>point out anything I would be really grateful.
>
>source code is available if anyone wants to see it
>
>many thanks
>
>
>.
>

[Alistair]

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:OF3gklE5DHA.3896@TK2MSFTNGP11.phx.gbl...

> >>>>> #01/01/04# AND endDate <= #31/01/04#))

>
> HEY!!!!! STOP USING AMBIGUOUS DATE FORMATS!!!!!!!!!!!!
>
> #2004-01-01# AND endDate <= #2004-01-31#
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

I know what you are saying but I fail to see what is ambiguous about this!!!

the SQL staement shows

startDate >= #01/01/04# AND endDate <= #31/01/04#

how could that be interpreted as anything other than 31st of Jan??

and if for some reason it was being interpreted wrongly then surely

startDate >= #01/02/04# AND endDate <= #29/02/04#

would also be wrong??...yet anything for february works!!!



however...if I was to take the date from the DB and split it, then rearange
it as yyyy-mm-dd you think this would work...I will try it first thing in
the morning.

[Alistair]

"Alistair" <news@*remove*alistairb.co.uk> wrote in message
news:101b0vd16n0jsdf@corp.supernews.com...

>
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> news:OF3gklE5DHA.3896@TK2MSFTNGP11.phx.gbl...

> > >>>>> #01/01/04# AND endDate <= #31/01/04#))

> >
> > HEY!!!!! STOP USING AMBIGUOUS DATE FORMATS!!!!!!!!!!!!
> >
> > #2004-01-01# AND endDate <= #2004-01-31#
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > [url]http://www.aspfaq.com/[/url]
> >
> >

>
> I know what you are saying but I fail to see what is ambiguous about

this!!!

>
> the SQL staement shows
>
> startDate >= #01/01/04# AND endDate <= #31/01/04#
>
> how could that be interpreted as anything other than 31st of Jan??
>
> and if for some reason it was being interpreted wrongly then surely
>
> startDate >= #01/02/04# AND endDate <= #29/02/04#
>
> would also be wrong??...yet anything for february works!!!
>
>
>
> however...if I was to take the date from the DB and split it, then

rearange

> it as yyyy-mm-dd you think this would work...I will try it first thing in
> the morning.
>
>

ok..couldn't wait until the morning...

it sems to be working...I have a few more tests to do though..

in doing so, I've actually discovered that I don't really need to change the
date formate...I was using Cdate which only gave me two digits for the
year..once I removed this I was getting all 4 digits and this meant no
ambiguous dates.

Hopefully I'll never have to trouble Aaron again as he's a genius with ASP
and I think I've upset him with my questions

thanks all

[Aaron Bertrand - MVP]

> Hopefully I'll never have to trouble Aaron again as he's a genius with ASP

> and I think I've upset him with my questions

No, not upset. Just amazed that this comes up every day; seems like
everyone has forgotten about Y2K and doesn't understand why software gets
confused when you pass it an ambiguous format...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]