Bug #6879 InvalidCast when using DATE_ADD-function
Submitted: 29 Nov 2004 17:26 Modified: 10 Dec 2004 19:31
Reporter: Eero Räisänen Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.2-gamma OS:Microsoft Windows (Windows XP/SP2)
Assigned to: Reggie Burnett CPU Architecture:Any

[29 Nov 2004 17:26] Eero Räisänen
Description:
mysql-version:4.1.7

Queries with DATE_ADD-function that used to work with ByteFx 0.76 
and mysql 4.0.18 won't work anymore. See "how to repeat"

You can get around the bug by using CAST(...AS DATETIME), but
I found that sometimes even casted return values are strings.
For example DATE_ADD(?someday, INTERVAL IFNULL(a.interval, b.interval) HOUR)
always returns string, even when using CAST.

How to repeat:
'This works

cmd.CommandText = "select date_add(now(), interval 1 hour)"
rdr = cmd.ExecuteReader
If rdr.Read() Then dt = rdr.GetDateTime(0)
rdr.Close()

'This throws InvalidCast Exception -- returned value is string, not datetime

cmd.CommandText = "select date_add(?someday, interval 1 hour)"
cmd.Parameters.Add("someday", Now)
rdr = cmd.ExecuteReader
If rdr.Read() Then dt = rdr.GetDateTime(0)
rdr.Close()

Suggested fix:
---
[10 Dec 2004 19:31] Reggie Burnett
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html