>From what I can tell no coercion is necessary when the query references only
DATETIME and TIMESTAMP columns but when the query includes literals you
don't get the correct results unless you format the dates. Take for example
a table with a DATETIME column (DT) and a TIMESTAMP column (TS). The
following two queries don't return what you expect:
mysql> SELECT DT, GREATEST(DT, '2004-02-24 04:59:21', \
20040224045919) AS G FROM Test;
+---------------------+----------------+
| DT | G |
+---------------------+----------------+
| 2004-02-24 04:59:20 | 20040224045920 |
| 2004-02-24 04:59:19 | 20040224045919 |
+---------------------+----------------+
mysql> SELECT TS, GREATEST(TS, '2004-02-24 04:59:21') \
AS G FROM Test;
+----------------+----------------+
| TS | G |
+----------------+----------------+
| 20040224045920 | 20040224045920 |
| 20040224045919 | 20040224045919 |
+----------------+----------------+
According to the documentation, GREATEST only does integer and string
comparisons so I guess the way DATETIME and TIMESTAMP values are compared
depends on their usage. Maybe the documentation could clarify this point.
> -----Original Message-----
> From: Egor Egorov [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 08, 2004 4:39 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Using GREATEST with DATETIME/TIMESTAMP values
>
> You shouldn't permorm explicit conversation if you use
> GREATEST() function with TIMESTAMP/DATETIME column types.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
