Bug #19765 CAST(), DATE() and TIME() returning wrong data type for dates?
| Submitted: | 12 May 2006 11:30 | Modified: | 3 Aug 2007 14:13 |
|---|---|---|---|
| Reporter: | Hartmut Holzgraefe | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0-BK, 5.1-BK | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[12 May 2006 11:30]
Hartmut Holzgraefe
[12 May 2006 11:46]
Mark Leith
Verified on 5.0 and 5.1 BK trees. 5.1: mysql> select cast(d as date)+0 from t1; +-------------------+ | cast(d as date)+0 | +-------------------+ | 2006 | +-------------------+ 1 row in set (0.00 sec) 5.0: mysql> select cast(d as date)+0 from t1; +-------------------+ | cast(d as date)+0 | +-------------------+ | 2006 | +-------------------+ 1 row in set (0.04 sec)
[12 May 2006 14:56]
Hartmut Holzgraefe
It's not clear from the documentation but i think DATE() and TIME() are also affected as these should return DATE and TIME and not STRING results.
[12 May 2006 15:01]
Hartmut Holzgraefe
mysqltest test case
Attachment: bug19765.tar.gz (application/x-gzip, text), 981 bytes.
[10 Jul 2006 17:15]
Martin Friebe
changing the result type of time() from string to time, might break existing functionality. See discusion on bug #11655 time can currently accept values outside the time-range as input, which would return a cut-off result if the result-type was time, instead of string select time("20000:00:01.1"); +-----------------------+ | time("20000:00:01.1") | +-----------------------+ | 20000:00:01.100000 | +-----------------------+ 1 row in set (0.00 sec) strictly spoken that is allready a bug, because we are suplying a string, with an invalid time as input. so other than tme_to_sec(), time() should never be able to return a result that needs cutting off I just thought I point it out.
[6 Nov 2006 19:52]
Chad MILLER
Similar to Bug#16546.
[3 Aug 2007 14:13]
Konstantin Osipov
This bug was fixed by the fix for Bug#16546 mysql> insert into t1 values (now()); Query OK, 1 row affected (0.00 sec) mysql> select d from t1; +------------+ | d | +------------+ | 2007-08-03 | +------------+ 1 row in set (0.00 sec) mysql> select d+0 from t1; +----------+ | d+0 | +----------+ | 20070803 | +----------+ 1 row in set (0.00 sec) mysql> select cast(d as date)+0 from t1; +-------------------+ | cast(d as date)+0 | +-------------------+ | 20070803 | +-------------------+ 1 row in set (0.00 sec)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.060 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.