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:
None 
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
Description:
Stumbled across this while checking bug #19747:

Adding 0 to a date field returns the date as integer in the YYYYMMDD format

Adding 0 to a CAST(... AS date)+0 returns only YYYY as if you were adding 0 
to a date string instead of a regular DATE value

How to repeat:
mysql> create table t1 (d date);

mysql> insert into t1 values (now);

mysql> select d from t1;
+------------+
| d          |
+------------+
| 2006-05-12 |
+------------+
1 row in set (0.00 sec)

mysql> select d+0 from t1;
+----------+
| d+0      |
+----------+
| 20060512 |
+----------+
1 row in set (0.00 sec)

mysql> select cast(d as date)+0 from t1;
+-------------------+
| cast(d as date)+0 |
+-------------------+
| 2006              |
+-------------------+
1 row in set (0.00 sec)
[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.