MySQL Bugs: #498: casting to date does not work properly

Bug #498	casting to date does not work properly
Submitted:	25 May 2003 6:15	Modified:	26 May 2003 4:19
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.13	OS:	Linux (Debian GNU/Linux)
Assigned to:		CPU Architecture:	Any

Description:
From the Debian Bug Tracking System. 
More information available at http://bugs.debian.org/190183. 
The bug was reported by Wichert Akkerman <wichert@wiggy.net>

MySQL does not implement casting to dates properly.

How to repeat:
mysql> select cast(now() as date);
+---------------------+
| cast(now() as date) |
+---------------------+
| 2003-04-22 16:51:15 |
+---------------------+
1 row in set (0.00 sec)

note that the time is not discarded as it should be. It should work
like this (example made using postgres):

template1=> select now(), cast(now() as date);
              now              |    now     
-------------------------------+------------
 2003-04-22 16:52:57.929235+02 | 2003-04-22
(1 row)

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Excuse me but why is this no bug? 

Is it wrong to assume that 
  SELECT cast(0 as date);
outputs the same string than
  CREATE TABLE t (d date);
  INSERT INTO t VALUES (0);
  SELECT d FROM t;
which would be "0000-00-00" (but is in reality "0")?

As the examples in the documentation suggerates this behaviour and at least postgres and maybe other SQL server implement it, you should at least comment it.

BTW: Why did you tell me to double-check not only the docs but also the information how to report a bug report? Did I do anything wrong? And doesn't this implies that there is a bug (why else I should learn to report it, hehe)?

Christian,

You are right that 
SELECT cast(0 as date); returns 0
but it was not your original report.

MySQL will treat 0 same way as 0000-00-00 or any other delimiter you use. Tj efact is that MySQL treats zero as special "error" value for Date/Time column types. So this result is consistant by trsm of MySQL implementation and is documented behaviour.

Refer to chapter 6.2.2 Date and Time Types for explanation of "Zero" value

# The ``zero'' values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values '0' or 0, which are easier to write.

No Bug here.

Your initial report was about result of:

mysql> select now(), cast(now() as date);
+---------------------+---------------------+
| now()               | cast(now() as date) |
+---------------------+---------------------+
| 2003-05-26 02:18:21 | 2003-05-26 02:18:21 |
+---------------------+---------------------+

This is fairly expected result and I don't see any bug here too.
MySQL still does not implement date/time columns with resolution smaller than 1 second and this is also docummented.

If you wanted to say that you wish it to be implemented then this should qualify as Feature-request not bug.

A short comment about this

In MysQL 4.0 the CAST to DATE, DATETIME and TIME only marks the column to be a specific type but doesn't change the value of the column.

This has changed somewhat in MySQL 4.1 where we convert the type to the requested one just before sending it to the client.

To convert things on the fly is something we have on our todo and will probably not appear until 5.0, as this is not a critical functionallity.

If you want to extract the date format, use LEFT or EXTRACT