MySQL Datetime --> VB.NET Datetime Conversion Prob Any solutions ??

Hi,
Can anyone provide me with a quick example of fetching a MySQL Datetime Field and converting it into native VB.NET DateTime format ?? Say for example my db contains a couple of fields: Field1, Field2, DateField... one of which is the default MySQL DateTime.

Say I'm using the following code to connect...

ConnectionString = "....."QueryString = "SELECT * FROM SomeTable"Dim myConnection As New MySql.Data.MySqlClient.MySqlConnection(ConnectionString)Dim myCommand As New MySql.Data.MySqlClient.MySqlCommand(QueryString, myConnection)Dim myReader As MySqlDataReaderTrymyConnection.Open()myReader = myCommand.ExecuteReaderWhile myReader.ReadField1 = myReader.Item ("Field1")Field2 = myReader.Item ("Field2")Field3 = myReader.Item ("DateField")End While'Close the connectionmyReader.Close()myConnection.Close()Catch exEvent As Exception'Do SomethingEnd Try

As you can see the query result is being broken up and stored into three separate fields, Field1, Field2 & Field3... Now:
1. If all these variables are declared as String - what would be the exact syntax to convert that DATETIME field into a String on the fly
2. If the Field3 variable is declared as VS.NET DateTime then what would be the syntax to convert it ..

I tried out a typecasting too:

While myReader.ReadField3 = ctype(myReader.Item ("DateField"), string)End While

In this case I receive an error message saying "Unable to convert mysql Date/Time value to System.DateTime"... BTW, am using the .NET Connector 1.0.4 and VS2003.

Any help would be really appreciated.

Thanks a million

I don't have actual solution for this and this might be a stupid suggestion but wouldn't using MySQL DATE_FORMAT to convert the data to suitable format first help?

I don't have actual solution for this and this might be a stupid suggestion but wouldn't using MySQL DATE_FORMAT to convert the data to suitable format first help?

<{POST_SNAPBACK}>

I've tried using all sorts of date formatting options - the problem is that even if you format the date otherwise, it is still not returned as a STRING type to VB. Instead VB still gets a pointer to that reformatted mysql date which is still in the native mysql DATETIME type and cannot be cast into a STRING directly or even with help of the mysql connector.net dll - I mean I'm sure it can be done using the connector.net - but I'm unable to and that why searching for the right syntax. If I can't perform this type conversion I'll have to resort to storing DATEs as CHAR/VARCHAR in MySQL - basically as STRINGS and not be able to use any of the great DATE/TIME calculation & handling features available in mysql....

Found a soution from MySQL Forums.

 

It worked with some additions to the connection string: "allow zero datetime=no" - that made the type cast work perfectly. Here's the working code again for your reference.

 

Dim BirthDate as Date................ConnectionString = "Server=.....;....; allow zero datetime=no"QueryString = "Select * from whatever"'Code for query............BirthDate = CType ( myReader.Item ( "BirthDate" ), Date )

That's it - now you can format the BirthDate variable to any desired format using the VB.Net "Format" command. Also this way you don't have to specify how to pre-format and fetch a field in the Query String. A simple "SELECT *" suffices - you just pick that specific field and typecast it to the native VB.NET format.

Migrating almost any database.MySQL Datetime --> VB.NET Datetime Conversion Prob

I use data loader for migrating almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. In my view this is a best Data Migration Tool 

Download Free : http://www.dbload.com/

-reply by J vincent