db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kent Spaulding <k...@iotabits.com>
Subject Re: Derby/Oracle - single SQL query?
Date Wed, 04 Feb 2009 20:47:15 GMT
Thanks Bernt.

That works in both DBs.

For the rest of what I've discovered:

Creating a to_date function also works.  Thanks for that too, as it  
looks like I'll need one for decode() as well.[1]

I'll use the {d '<format>'} syntax, plus a decode function I'll write.

Thanks again.
--Kent

>> This works in Derby, not Oracle:
>>
>>   and cast(d.insertion_date as date) >= '2009-01-01'
>>   and cast(d.insertion_date as date) <= '2009-02-28'
>>
>> This works in Oracle, not Derby:
>>
>>   and cast(d.insertion_date as date) >= to_date('2009-01-01', 'YYYY-
>> MM-DD')
>>   and cast(d.insertion_date as date) <= to_date('2009-02-28', 'YYYY-
>> MM-DD')
>>
>> Have to use a function to convert the literal, AFAIK.
>>
>> Is there some query format that will for both?
>
> Try
>    and cast(d.insertion_date as date) >= {d '2009-01-01'}
>    and cast(d.insertion_date as date) <= {d '2009-02-28'}
>
>> If no, can I add a to_date function to Derby?
>>
>> Thanks in advance,
>> --Kent

[1] -
	/**
	 *<code>
	 *  CREATE FUNCTION TO_DATE
	 *  ( dateStr VARCHAR(40) , dateFormat VARCHAR(40) )
	 *  RETURNS DATE
	 *  PARAMETER STYLE JAVA
	 *  NO SQL LANGUAGE JAVA
	 *  EXTERNAL NAME 'com.iotabits.derby.Extensions.toDate'
	 *</code>
	 */
	public static Date toDate(String dateStr, String dateFormat) {
                  // super simplistic conversion of SQL Date format  
string to Java Date format string.
		 String format = dateFormat.replace('Y', 'y');
		 format = format.replace('D', 'd');
		 // move this to TLS?
                  DateFormat formatter = new SimpleDateFormat(format);
		 try {
		     returnnew Date(formatter.parse(dateStr).getTime());
		  } catch (ParseException e) {
		     // logging goes here
		  }
		  return null;
	}

Mime
View raw message