RE: Date casting problem - Kevin Turner - com.mysql.lists.dotnet

14 messages in com.mysql.lists.dotnetRE: Date casting problem

From	Sent On	Attachments
Kevin Turner	13 Feb 2005 01:07
Jordan Sparks	13 Feb 2005 06:56
Kevin Turner	13 Feb 2005 07:09
Jordan Sparks	13 Feb 2005 07:17
Ben Reichelt	13 Feb 2005 08:27
Kevin Turner	13 Feb 2005 09:14
James Moore	13 Feb 2005 10:28
James Moore	13 Feb 2005 10:43
Jorge Bastos	13 Feb 2005 13:51
Reggie Burnett	23 Feb 2005 06:01
Kendal Goodrich	24 Feb 2005 07:40
Guy Platt	12 Apr 2005 07:29
Guy Platt	12 Apr 2005 07:45
SGr...@unimin.com	12 Apr 2005 13:40

Subject:	RE: Date casting problem
From:	Kevin Turner (kevi...@coraltree.co.uk)
Date:	02/13/2005 09:14:09 AM
List:	com.mysql.lists.dotnet

I guess what worries me most is not the problem with null dates etc, but the
fact that with one version of the connector I can cast a MySql date field
straight into a .Net Date field, but as soon as I upgrade to a new version of
the connector my application crashes when I attempt such a cast. It might be
better if it failed to compile - at least I would have a warning.

As I haven't been using Mysql with .Net for long I may have missed a big banner
at some point warning that this would happen. It is very disconcerting to have
ones rock solid application crash in a big heap when you haven't even touched
any code. Surely implementing changes like this is a "no no" for any provider?
Why was this change introduced anyway - the direct cast to a .Net date field
worked perfectly, so why change it ?

-----Original Message----- From: Ben Reichelt [mailto:ben....@gmail.com] Sent: 13 February 2005 16:28 To: dot...@lists.mysql.com Subject: Re: Date casting problem

I wonder if using .net 2.0 will make this problem a little easier to deal with, by using nullable value types, this way, if a date field is null, you can have a null DateTime like this:

DateTime? dt = dr["myDateField"] as DateTime?;

On Sun, 13 Feb 2005 07:17:39 -0800, Jordan Sparks <jspa...@free-dental.com> wrote:

I agree. I would like null dates as well, but there's no such thing as null dates in .NET. It was in the beta, but gone by production. The limiting factor is .NET. So use 1-1-1 as your 'null' date, and use internal logic to test for that value. But the data in the database should mirror that convention.

-----Original Message----- From: Kevin Turner [mailto:kevi...@coraltree.co.uk] Sent: Sunday, February 13, 2005 7:10 AM To: dot...@lists.mysql.com Subject: RE: Date casting problem

Thanks for the suggestion. I will try it.

Dunno if I am storing illegal dates though really - more the fact that I want some date fields to be null. Surely if the database can store a value in a certain way, the connector should allow for it?

-----Original Message----- From: Jordan Sparks [mailto:jspa...@free-dental.com] Sent: 13 February 2005 14:57 To: dot...@lists.mysql.com Subject: RE: Date casting problem

Sorry, I don't know the vb syntax.

Here's how I would do it in C#. First to a string, then to a .NET datetime, and finally to a string for display. Yes, you have to write a small function to do this for every incoming date, but you only have to write it once, and it's very small:

public static DateTime GetDate(string myString){ try{ return (DateTime.Parse(myString)); } catch{ return DateTime.MinValue; } }

And you would use it like this:

strDate=MysqlIn.GetDate(dr["myDate"].ToString()).ToString("d");

But the try statement can cause a slowdown, so you either fancy up my example to check for zeros instead of using a try, or you change your database columns to use 1-1-1 as the default. And really, how hard is this last to do? It's not the fault of the connector that you're trying to store illegal dates in your database. Keep it clean and just change your database.

-----Original Message----- From: Kevin Turner [mailto:kevi...@coraltree.co.uk] Sent: Sunday, February 13, 2005 1:08 AM To: dot...@lists.mysql.com Subject: Date casting problem

My database contains date fields that can legitimately contain "illegal" date values like 0000-00-00.

Until recently I was using the ByteFx 0.76 version of the driver and everything worked fine worked fine. However, I was worried that connection pooling wasn't right so decided to convert to the new MySql.Data 1.0.4 version.

My first problem was that have my web pages started to crash with a previously reported problem where the new driver does not like illegal (all zero) dates on the database. Well, on my application this is a common occurrence so I added the "Allow Zero Datetime" = "True" to my connection string to get around it.

Now my problem is even worse. In many places in my code I have instructions like this:

(assume "dr" is a datarow retrieved from a dataset)

With Threading.Thread.CurrentThread strDate = CType(dr("myDate"), Date).ToString("d", .CurrentUICulture) End With

This works perfectly with the ByteFx version and it converts the database date to a string format that is correct for the culture for the current thread (i.e. in the UK its dd/MM/yyyy format and in the US its M/d/yyyy format).

With the new driver this statement is an illegal cast. All I can do is cast the database field to a Mysql.Data.MySqlDateTime - which does not have the appropriate ToString() method to use the threads culture.

To say this is a disaster for me is an understatement. I have to revert to ByteFx 0.76 for now until somebody can tell me the solution. I know I can probably use the GetDateTime method on a reader, but what can I do with a datarow from a dataset ? Do I have to create my own function to cast to a Mysql.Datat.Type.MysqlDateTime, then use ToString, then cobble the correct date format from that ? Yuk !

"NOTICE: The information in this electronic mail transmission is intended by CoralTree Systems Ltd for the use of the named individuals or entity to which it is directed and may contain information that is privileged or otherwise confidential. If you have received this electronic mail transmission in error, please delete it from your system without copying or forwarding it, and notify the sender of the error by reply email or by telephone, so that the sender's address records can be corrected."