You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Max Ten <ma...@gmail.com> on 2006/03/01 06:53:30 UTC
Re: Derby Function
if i'm not misunderstood your explanation, tats mean i have to store ex:
2006-10-31 to 3 different columns, (Yr) for 2006, (Mth) for 10 and (Dy) for
31.
By this way i can use the function "GROUP BY" for those columns.
Anyway, will it be any enhancement for function "GROUP BY" in future?
Thanks Stanley.
Re: Derby Function
Posted by Stanley Bradbury <St...@gmail.com>.
Max Ten wrote:
> if i'm not misunderstood your explanation, tats mean i have to store
> ex: 2006-10-31 to 3 different columns, (Yr) for 2006, (Mth) for 10 and
> (Dy) for 31.
> By this way i can use the function "GROUP BY" for those columns.
>
> Anyway, will it be any enhancement for function "GROUP BY" in future?
> Thanks Stanley.
>
Hi -
I was unclear. You store the years in one column, the days (1-31) in
another column (or table) and the months (1-12) in a third column (or
table). All these tables do is translate the date part values returned
by the functions year(), month(), day() to column values that are
referenced in the select-list and can also be used in the GROUP BY
clause. The table would look something like this:
Yr Mn Dy
2006 1 1
2005 2 2
2004 3 3
. . .
1995 12 12
. . .
1978 null 31
. . .
1818 null null
In your query you need to alias the table three time (like it was three
different tables) so the equalities match records independantly of the
other values (e.g. the Mn and Dy value do not need to be associated with
the Yr value or each other).
If this is confusing just use a seperate table to each date part (Year,
Month and Day).