Re: error in comparing date with datetime

On Tue, 23 Mar 2004 20:21:50 +0200
  Sinisa Milivojevic <[email protected]> wrote:
>Paul B van den Berg writes:
>> Description:
>> 	a datetime with no time-information should be equal to a date
>> 	if de date-parts are identical.
>> How-To-Repeat:
>> 	create table t (d1 date, d2 datetime);
>> 	insert into t values (20040320,20040320);
>> 	select d2=d1 from t;
>> 	+-------+
>> 	| d2=d1 |
>> 	+-------+
>> 	|     0 |
>> 	+-------+
>> Fix:
>> 	select d2=d1+interval 0 second from t;
>> 	+-------------------------+
>> 	| d2=d1+interval 0 second |
>> 	+-------------------------+
>> 	|                       1 |
>> 	+-------------------------+
>> 
>
>Hi!
>
>Yes, the above is expected behaviour. 
>
>You can not compare different column types unless you do some type of
>conversion.
>
>It can be done as above or with CAST() commands.
>
>-- 
>
>Sincerely,
>
>-- 
>For technical support contracts, go to https://order.mysql.com/?ref=msmi
>   __  ___     ___ ____  __
>  /  |/  /_ __/ __/ __ \/ /    Mr. Sinisa Milivojevic <[email protected]>
> / /|_/ / // /\ \/ /_/ / /__   MySQL AB
>/_/  /_/\_, /___/\___\_\___/   Full time Developer and Support Coordinator
>       <___/   www.mysql.com   Larnaca, Cyprus
>
>Meet the MySQL at User Conference ! (April 14-16, 2004)
>http://www.mysql.com/uc2004/
>

It took me some time, but now I have found the latest draft SQL 
specification
at http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/DocumentsView?openview
(I don't want to pay for the official document).
Document 32N1006-WD9075-02-FOUNDATION-2003-09.PDF states:
[quote]
3.1.6.5 comparable (of a pair of values): 
Capable of being compared, according to the rules of Subclause 8.2,
"<comparison predicate>". In most, but not all, cases, the values of a data 
type
can be compared one with another. For the specification of comparability of 
individual data types, see Subclause 4.2, "Character strings", through 
Subclause
4.10, "Collection types".

4.6.2 Datetimes
Table 2, "Fields in datetime values", specifies the fields that can make up 
a 
datetime value; a datetime value is made up of a subset of those fields. Not 
all
of the fields shown are required to be in the subset, but every field that 
appears in the table between the first included primary field and the last 
included primary field shall also be included. If either time zone field is 
in 
the subset, then both of them shall be included.

   Table 2 -- Fields in datetime values
  Keyword                  Meaning
  YEAR                     Year
  MONTH                    Month within year
  DAY                      Day within month
  HOUR                     Hour within day
  MINUTE                   Minute within hour
  SECOND                   Second and possibly fraction of a second within 
minute
  TIMEZONE_HOUR            Hour value of time zone displacement
  TIMEZONE_MINUTE          Minute value of time zone displacement

There is an ordering of the significance of <primary datetime field>s. This 
is, 
from most significant to least significant: 
YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

There are three classes of datetime data types defined within this part of 
ISO/IEC 9075:
-- DATE -- contains the <primary datetime field>s YEAR, MONTH, and DAY.
-- TIME -- contains the <primary datetime field>s HOUR, MINUTE, and SECOND.
-- TIMESTAMP -- contains the <primary datetime field>s YEAR, MONTH, DAY, 
HOUR, 
    MINUTE, and SECOND.

Items of type datetime are comparable only if they have the same 
<primary datetime field>s.

[endquote]

I was wrong in my initial email where I stated that a date and a datetime 
should 
be equal. However, the SQL-specification states that a date and a datetime 
cannot be compared at all.
Shouldn't MySQL report an error if you try to do it?

Regards, Paul
-- 
Paul B van den Berg, Manager InterAction Database
Social Pharmacy, Pharmacoepidemiology & Pharmacotherapeutics, 
University of Groningen  tel +31 50 3633331 fax +31 50 3632772

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]