db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Derby Function
Date Wed, 01 Mar 2006 23:15:44 GMT
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).

View raw message