Count Statement Error
A user on an Informix SE database got error 1210 ("Date could not be converted to month/day/year format") from `select count(*) from cardact where act_date > "070799"`, even though a plain SELECT with the same filter and a count with `=` worked. Suggestions were that the table may contain invalid date values, to try a more conventional literal like "7/7/99", and that date string interpretation depends on the DBDATE environment variable setting. The poster never confirmed a fix, so no definite resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a rather large table called "cardact" with about 1,180,000 records on
an SE database.
The sql command I run through dbaccess is
select count(*) from cardact
where act_date > "070799"
The error message I get is
1210: Date could not be converted to month/day/year format.
Things I have tried (not necessarily in this order):
* bcheck the cardact table.
* added an index on act_date.
* The sql commands:
select * from cardact where act_date > "070799"
select count(*) from cardact where act_date = "070799"
both correctly retrieve data.
Can any one help me here as to why this error is occuring and what can I do
about it.
Thanks in Advance
Regards
Brett Schubert
Works for me! Did you try a more standard date format such as "7/7/99"?
But your string works on my tables. Unless your table contains some
date values which are invalid dates!
Art S. Kagel
Brett Schubert wrote:
>
> I have a rather large table called "cardact" with about 1,180,000 records on
> an SE database.
>
> The sql command I run through dbaccess is
>
> select count(*) from cardact
> where act_date > "070799">
> The error message I get is
>
> 1210: Date could not be converted to month/day/year format.>
> Things I have tried (not necessarily in this order):
> * bcheck the cardact table.
> * added an index on act_date.
> * The sql commands:
>
> select * from cardact where act_date > "070799"
> select count(*) from cardact where act_date = "070799">
> both correctly retrieve data.
>
> Can any one help me here as to why this error is occuring and what can I do
> about it.
>
> Thanks in Advance
>
> Regards
> Brett Schubert
All this relies on your DBDATE environment variable. Look it up in the
manual.
Thats why a certain format works for some users and not others.
Art S. Kagel <kagel@bloomberg.net> wrote in message
news:37851A77.F62A2CB7@bloomberg.net...
> Works for me! Did you try a more standard date format such as "7/7/99"?
> But your string works on my tables. Unless your table contains some
> date values which are invalid dates!
>
> Art S. Kagel
>
> Brett Schubert wrote:
> >
> > I have a rather large table called "cardact" with about 1,180,000
records on
> > an SE database.
> >
> > The sql command I run through dbaccess is
> >
> > select count(*) from cardact
> > where act_date > "070799"> >
> > The error message I get is
> >
> > 1210: Date could not be converted to month/day/year format.> >
> > Things I have tried (not necessarily in this order):
> > * bcheck the cardact table.
> > * added an index on act_date.
> > * The sql commands:
> >
> > select * from cardact where act_date > "070799"
> > select count(*) from cardact where act_date = "070799"> >
> > both correctly retrieve data.
> >
> > Can any one help me here as to why this error is occuring and what can I
do
> > about it.
> >
> > Thanks in Advance
> >
> > Regards
> > Brett Schubert