From: Jordan Sparks
Date: February 13 2005 2:56pm
Subject: RE: Date casting problem
List-Archive: http://lists.mysql.com/dotnet/479
Message-Id: <001001c511dc$3d636a90$6401a8c0@jordans>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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.


Jordan Sparks
 

-----Original Message-----
From: Kevin Turner [mailto:kevin.turner@stripped] 
Sent: Sunday, February 13, 2005 1:08 AM
To: dotnet@stripped
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 !

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005
 

"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."