On 13-8-2013 06:41, Maya Opperman wrote:
>> Mark Rotteveel wrote:
>> I am unable to reproduce it. Could you create a simple reproduction script
>> that creates the table, adds the testdata and includes the query?
>
>> My reproduction is:
>
>> CREATE TABLE int_date_table
>> (
>> intfield INTEGER,
>> datefield DATE,
>> timestampfield TIMESTAMP
>> );
>
> Make datefield a TIMESTAMP, not a DATE, and you should get the error as well.
Sorry for the late answer. If both are DATE or both are TIMESTAMP
everything is OK. When one is a DATE and the other is a TIMESTAMP it
breaks down
So these two work:
SELECT CASE WHEN INTFIELD = 1 THEN DATEFIELD ELSE DATEFIELD END
FROM INT_DATE_TABLE a
SELECT CASE WHEN INTFIELD = 1 THEN TIMESTAMPFIELD ELSE TIMESTAMPFIELD END
FROM INT_DATE_TABLE a
But this one doesn't work:
SELECT CASE WHEN INTFIELD = 1 THEN DATEFIELD ELSE TIMESTAMPFIELD END
FROM INT_DATE_TABLE a
So the original example in your first post should simply work, for the
example of your second post you should replace your use of (cast('Today'
as date) + 1) with (cast('Today' as timestamp) + 1) or simply
CURRENT_TIMESTAMP + 1.
Looking at SQL:2011 Foundation section 9.5 'Result of Data type
combinations' (referred to from 6.12 <case expression>):
"e) If some data type in DTS is a datetime data type, then every data
type in DTS shall be a datetime data type having the same datetime
fields. The result data type is a datetime data type having the same
datetime fields, whose fractional seconds precision is the largest of
the fractional seconds precisions in DTS. If some data type in DTS has a
time zone displacement value, then the result has a time zone
displacement value; otherwise, the result does not have a time zone
displacement value."
I believe this should be interpreted that the current behavior is
correct as DATE and TIMESTAMP do not have "the same datetime fields".
Section 4.6.2 (datetimes) also says:
"Items of type datetime are comparable only if they have the same
<primary datetime field>s."
and
"A datetime is assignable to a site only if the source and target of the
assignment are both of type DATE, or both of type TIME (regardless
whether WITH TIME ZONE or WITHOUT TIME ZONE is specified or implicit),
or both of type TIMESTAMP (regardless whether WITH TIME ZONE or WITHOUT
TIME ZONE is specified or implicit)."
This seems to imply that the conversion should not be implicit but
always explicit. It would also mean that other areas of Firebird don't
comply eg equality allows a TIMESTAMP and DATE to be compared (equals,
<, >, BETWEEN etc).
Mark
--
Mark Rotteveel
