Re: Tests in SQL

[Rick Hillegas]

Hi Nicolas,

I don't know if anyone got back to you on this one. You can always 
create your own function to do this. The java code would look something 
like this:

   public    static    String    dayOfWeek( java.sql.Date date )
       throws Exception
   {
       GregorianCalendar    calendar = new GregorianCalendar();
      
       calendar.setTime( date );

       int                    weekday = calendar.get( 
Calendar.DAY_OF_WEEK );

       switch( weekday )
       {
           case 1: return "Sunday";
           case 2: return "Monday";
           case 3: return "Tuesday";
           case 4: return "Wednesday";
           case 5: return "Thursday";
           case 6: return "Friday";
           case 7: return "Saturday";

           default: throw new SQLException( "Unknown weekday: " + 
weekday );
       }
   }

Then you could declare this as a function and use it as you described:

create function app.dayOfWeek
   ( dateValue date )
   returns varchar( 8 )
   parameter style java
   no sql
   language java
   external name 'z.dayOfWeek'
;
  

values app.dayOfWeek( date( '2005-10-03' ) );

Cheers,
-Rick

Nicolas Dufour wrote:

Thanks

Derby is really complete :)

However, I didnt find in the builtin function a way to retrieve the 
day of the week of a date or a timestamp, I mean monday, tuesday 
...and so on.
Is there such function anywhere ?

Nicolas

Suavi Ali Demir wrote:

SELECT    CASE WHEN COL1 is NULL THEN 0 ELSE 1 END    FROM MYTABLE
 
Regards,
Ali

*/Nicolas Dufour <[EMAIL PROTECTED]>/* wrote:

    Hello

    I try to put tests in select clause.
    I mean I have a field which can be null and I want to create a 
select
    which return 0 when the field is null and 1 in the other case.
    Is it possible to do that in Derby ?
    I try also to look in a string and if i recognize a pattern
    extract two
    parts of it and if not just take the all value.
    How can i do that ?

    Thanks

    Nicolas