Board index » delphi » How to write Date Group in Interbase?

How to write Date Group in Interbase?

Hi,

I have a table
( FileId, EnteredDate, ... )

In Access, before I create report, I write like this

SELECT DatePart('yyyy',EnteredDate),DatePart('m',EnteredDate),Count(FileId)
  FROM Files
  GROUP BY DatePart('yyyy',EnteredDate), DatePart('m',EnteredDate)

But it does not work with Interbase( Interbase does not support function in
group..)
How can I do?
Any advice?

Thanks in advance.

James.

 

Re:How to write Date Group in Interbase?


Interbase (based on SQL 92) won't allow anything but a field name in a GROUP
BY statement.  You either need to create a calculated field and then group
by that, or create a view where one of the columns of the view is your
calculated date field.

Dan

Quote
James Chou <ja...@radicalogic.com> wrote in message

news:8b6j3v$ab111@bornews.borland.com...
Quote
> Hi,

> I have a table
> ( FileId, EnteredDate, ... )

> In Access, before I create report, I write like this

> SELECT

DatePart('yyyy',EnteredDate),DatePart('m',EnteredDate),Count(FileId)
Quote
>   FROM Files
>   GROUP BY DatePart('yyyy',EnteredDate), DatePart('m',EnteredDate)

> But it does not work with Interbase( Interbase does not support function
in
> group..)
> How can I do?
> Any advice?

> Thanks in advance.

> James.

Re:How to write Date Group in Interbase?


You could write a stored procedure, return the dateparts and then
do a

SELECT * FROM STOREDPROC GROUP BY .....

but AFAIK you cannot group on calculated fields in Interbase SQL.

Quote
James Chou <ja...@radicalogic.com> wrote in message

news:8b6j3v$ab111@bornews.borland.com...
Quote
> Hi,

> I have a table
> ( FileId, EnteredDate, ... )

> In Access, before I create report, I write like this

> SELECT

DatePart('yyyy',EnteredDate),DatePart('m',EnteredDate),Count(FileId)
Quote
>   FROM Files
>   GROUP BY DatePart('yyyy',EnteredDate), DatePart('m',EnteredDate)

> But it does not work with Interbase( Interbase does not support function
in
> group..)
> How can I do?
> Any advice?

> Thanks in advance.

> James.

Other Threads