Date data type?

[LoveMercy]

In VB.NET it should be:

#M/dd/yyyy#

In my version of MySQL it should be:

yyyy-mm-dd

How can I get the date data type in VB.NET to change format so MySQL is able to fill the date field? Or vice versa, how can I get the date data type in MySQL to change format so it's able to fill the date field?

 

[JohnH]

I think you're confusing Date data type with String data type. What you are displaying above is different string formats of date values, the date values themselves are not different.

 

[LoveMercy]

When I wrote DATE_FORMAT('" & objTest.TestDate & "', '%Y-%m-%d') in the SQL syntax I got the format and the month right, but the year and day doesn't make sense.

 

[JohnH]

Date_format function converts a date to a string, there is not much need for it. Formatting a date string is only needed for display purposes, something the view control should be taking care of, when handling date data handle it as dates all the way. Queries should be made with parameters, not adding strings together, for example:

Dim dt As New Date(2007, 12, 12)
Dim myCommand As New MySqlCommand
myCommand.CommandText = "SELECT user_id FROM user WHERE birthdate = ?somedate"
myCommand.Parameters.AddWithValue("?somedate", dt)

 

[LoveMercy]

Without any date formatting in the SQL syntax the date #11/30/2006# in VB.NET turns into 2030-11-20 in MySQL. Frustrating...

 

[JohnH]

You have done something wrong along the way for that to happen. A date object as such doesn't have any format, it is culture and string formatting independent, simply a date. While both the .Net data type and MySql data type store dates internally in a specific format, that is of no concern when you handle the date object in code. The Connector library provide the tools to send date data back and forth between .Net and the database, this process is transparent for the programmer.

 

[LoveMercy]

It's solved now. I forgot the fact that the SQL syntax is a string, so the date can't be stored with the date data type.

 

[JohnH]

It's solved now. I forgot the fact that the SQL syntax is a string, so the date can't be stored with the date data type.

Yes it can, because you use Parameters when adding parameters to a SQL query. There are many benefits for doing this the right way. Ref post 4 and the small sample code there:

Queries should be made with parameters, not adding strings together.