Re: Tests in SQL

[Rick Hillegas <Ri...@Sun.COM>]

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 <ni...@neometsys.fr>/* 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
>>
>
>