db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Week number of the year CASE statement query
Date Sun, 21 Aug 2011 10:41:48 GMT
On 20/08/2011 20:44, Morten Mikkelsen wrote:
> Could you elaborate on your initial suggestion about using
> Calendar.WEEK_OF_YEAR instead of the ugly case-thing to make up for my
> temp_week_counts?

Create a Java method:
   public static final int weekNumber (java.sql.Date date) {
     Calendar c = Calendar.getInstance();
     c.setTime(date);
     return c.get(Calendar.WEEK_OF_YEAR);
   }

Now create a function in SQL:
   CREATE FUNCTION WeekNumber(theDate DATE)
     RETURNS INTEGER
     PARAMETER STYLE Java
     LANGUAGE Java
     EXTERNAL NAME 'packageName.ClassName.weekNumber';

...and then just use "WeekNumber(aDate)" in the SELECT statement.

A bit simpler than the ugly CASE!

Cheers,

------------------------------------------------------------------------
  John English | My old University of Brighton home page is still here:
               | http://www.it.brighton.ac.uk/staff/je
------------------------------------------------------------------------

Mime
View raw message