Description:
I wish someone would address the J Connector issue with null datetimes.  It's not right that the JDBC driver should report "" instead of 0000-00-00 00:00:00 for null datetimes.  If MySql decides that's how they want their datetime, why can't the JDBC driver output the info exactly as mysql does in the command line?  Also I would like to note that specifying just a time like 12:00:00 in a null date time field outputs this: 0002-11-30 12:00:00.

I should also like to mention that reporting {ts '1970-01-01 00:00:00'} as a null time in a null time field is also less than adequate.  It should just report 00:00:00 just as it does when you select it in a query in command line mysql.

How to repeat:
1. Set a datetime field to null in mysql and it does not report 0000-00-00 00:00:00 but rather an empty string: ""

2. Set the time portion of a datetime field in mysql like 0000-00-00 12:00:00 and the driver reports 0002-11-30 12:00:00

3. Set a date field to 0000-00-00 and the driver reports an empty string: ""

4. Set a time field to 00:00:00 and the driver reports {ts '1970-01-01 00:00:00'}

Suggested fix:
1. Please report null datetime fields as mysql does: 0000-00-00 00:00:00
2. Please report null date fields as mysql does: 0000-00-00
3. Please report null time fields as mysql does: 00:00:00
4. Please report time-only filled datetime fields as: 0000-00-00 12:00:00 [Replace 12:00:00 with any time]

> 1. Set a datetime field to null in mysql and it does not report 0000-00-00
> 00:00:00 but rather an empty string: ""

It should report NULL. Please show some example code where it returns an empty string (I have just tested this). Are you using a tag library or utility code that automatically converts NULL to "" for convenience?

> 2. Set the time portion of a datetime field in mysql like 0000-00-00
> 12:00:00 and the driver reports 0002-11-30 12:00:00

This is because Java itself can not represent a date of '0000-00-00', therefore it is an invalid datetime. If you want to store _only_ time, then you should use the _Time_ type, not datetime.

> 3. Set a date field to 0000-00-00 and the driver reports an empty string:
> ""

See number 1.

> 
> 4. Set a time field to 00:00:00 and the driver reports {ts '1970-01-01
> 00:00:00'}

Which is how it is defined in JDBC (http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Time.html):

"The date components should be set to the "zero epoch" value of January 1, 1970 and should not be accessed."

so this is correct. 

However, where are you getting escape codes from? The driver _never_ adds escape codes. Please add example code to this bug report if you believe this is still a driver issue.

> 
> Suggested fix:
> 1. Please report null datetime fields as mysql does: 0000-00-00 00:00:00
> 2. Please report null date fields as mysql does: 0000-00-00
> 3. Please report null time fields as mysql does: 00:00:00

MySQL doesn't report NULL datetimes as '0000-00-00' unless you've defined the columns as NOT NULL, so it creates a 'sentinel' datetime value that is all zeroes.  Java can not represent these dates internally, so the driver converts them to NULL, not empty strings.

> 4. Please report time-only filled datetime fields as: 0000-00-00 12:00:00
> [Replace 12:00:00 with any time]

As in the answer above, use the correct type, TIME. Java can't represent a date with years, months or days set to zero.

I'm using Coldfusion MX with the cfquery tag and the mysql jdbc to grab information from a MySql 4.0.18 database.  It seems that null numbers are also reported as empty strings and I don't know if CF is doing that or if the jdbc driver is.  

I understand that JDBC conforms to a standard but that standard is messed up.  So it is wrong to report exactly as Mysql sees the data?  How is it ODBC can report the data exactly but JDBC cannot?  Since 0000-00-00 is not a real date, then perhaps I should file a bug with the mysql database programmers and have them change 0000-00-00 to NULL or empty string?

> I'm using Coldfusion MX with the cfquery tag and the mysql jdbc to grab
> information from a MySql 4.0.18 database.  It seems that null numbers
> are also reported as empty strings and I don't know if CF is doing that
> or if the jdbc driver is.  

ColdFusion is. NULL is <> "" as far as JDBC and the SQL standard is concerned.

> 
> I understand that JDBC conforms to a standard but that standard is
> messed up.  

The standard is what it is. Java's API is very standards-compliant. a 0 year, with a 0 month and a 0 day is an illegal value. There is absolutely no way to represent '0000-00-00' as a Date in Java.

> So it is wrong to report exactly as Mysql sees the data? 
> How is it ODBC can report the data exactly but JDBC cannot?  

ODBC doesn't have the same mapping to a DATE type that JDBC does. In ODBC, the date type is just a structure. In JDBC, it is based on java.util.Date which already has predefined behaviors.

> Since
> 0000-00-00 is not a real date, then perhaps I should file a bug with
> the mysql database programmers and have them change 0000-00-00 to NULL
> or empty string?

0000-00-00 is only used when you've either entered an _illegal_ date, or when you have entered NULL into a DATETIME column that has been created as NOT NULL. Are you sure that your schema is correct for the way you are using it?

>0000-00-00 is only used when you've either entered an _illegal_ date, or when
>you have entered NULL into a DATETIME column that has been created as NOT NULL.
>Are you sure that your schema is correct for the way you are using it?

I have a problem with this because in my web-based program, I don't always need to store a date/datetime value in the datetime field because in certain instances, it doesn't call for it.  So if CF worked properly and reported a NULL value for empty dates/datetimes, then I wouldn't be able to check against it because CF doesn't know what to do with NULL values.

In places where people use the datetime field type and only store the time, why not just split the date off of the time portion and parse the time as if it were a time field so you don't get that illegal 0002-11-30 date?