Question date to date

ideprize

Well-known member
Joined
Oct 19, 2011
Messages
97
Programming Experience
10+
Hi All

Been searching all over the net but all of the answers are relative to converting dates to string format. I need to convert the current date into a DATE format of yyyy-mm-dd to save in a mysql DATE field of a mysql table. If I try to update/insert a date with the American format mm/dd/yyyy the date field in the mysql table is set to 0000-00-00. I have tried the parse and parseExact functions but I'm not getting to the promise land. Any code examples would be greatly appreciated.

Respectfully,
IDEPRIZE
 
I'm afraid you're confused. Dates have no format. The reason that all the examples you see are about formatting Dates as Strings is that that is the only way you can format dates. A binary date is just a number. That goes for VB and MySQL. How they display dates is a different matter but only text can be displayed so to display a date in some format, it must be converted to text.

The first thing to do is determine exactly what data type your MySQL database column is. Either it is a data type that stores binary dates or it is a data type that stores text. If it's the latter then that's bad database design and you should change it to the former if that is within your power.

Assuming that your database column is a binary date data type then you simply save a Date object to it from VB and format is never an issue. Format is only relevant when working with Strings that contain a representation of a date. If you have a binary Date in VB and you save that to the database then there's never any text so there's no format. To ensure that all values saved to the database always stay in their native binary form, you should always use parameters to insert values into SQL code; NEVER string concatenation. If you use string concatenation then you're converting your Dates to Strings and then format does become an issue. Not only that, MySQL then has to convert them back to binary dates anyway.

To learn why and how to use parameters, follow the Blog link in my signature and check out my post on Parameters In ADO.NET. It first demonstrates what NOT to do, which also includes formatting Dates a Strings such that it can work. Doing it that way is cumbersome and still leaves you open to various other issues though, so make sure that you read the whole thing and do it the proper way.
 
Hi Jim thanks for the response and you are correct - I am confused. This is reminiscent of working with floating point representation. Why I got off on that tangent I am not sure but it surely has something to do with humanity! Anyway your comments sent me off on the road that solved the problem. I had to use a string variable and set it to the format of "yyyy-MM-dd" in order to update the "date" mysql table field. Learn something everyday! Again Jim thanks for your help.
Respectfully,
IDEPRIZE
 
Hi Jim, again!

I forgot to mention in my previous response that I attempted to update the table field using a "date" based variable but that sets the field to "0000-00-00". I will look some more through your docs but I was not successful until I used the string variable for the update.

IDEPRIZE
 
It is date (not datetime). I created the table and defined it as such.

I didn't ask you what the data type of the database column is. I asked you what the DataType property of the DataColumn was in the DataTable generated when retrieveing that data from the database.
 
I am not sure I understand the distinction as clearly as you do but for what it is worth I am doing a "insert" into the table after the connection to the database is made. There is no "retrieval" of the "record" data in this case other than the "meta data" of its structure.
It sounds like, however, there is something you can teach me.
Ideprize
 
Here it is:
'new po - first create pohead record
Dim ddte As Date = DateTime.Now
Dim dte As String
dte = ddte.ToString("yyyy-MM-dd")


SQLstr = "Insert into pohead(pohdponum,pohddte) values('" & strPOnum & "', '" & dte & "');" 'strPOnum is captured from a textbox input.
SQLcmd = New MySqlCommand(SQLstr, SQLCnn) 'SQLCnn is set at connection time in code "above"
SQLcmd.ExecuteNonQuery() 'SQLstr, SQLcmd, and SQLCnn are defined, again, in code above
SQLcmd = Nothing

This works. If I try to change the value of "dte" from a string to a date type it sets the pohddte field to 0000-00-00.
If there is a better way to do this that works then please educate me. My first thoughts were it had to be a "date" construct because the target field type was date. Lost 5 hours on that "circle".

Respectfully,
Ideprize
 
But it's not a Date even if you use a Date because you're still converting the Date to a String. It IS supposed to be a Date but you aren't and weren't using a Date. You should always be using parameters to insert values into SQL code regardless of the data type; never string concatenation. That way, all your values stay in their original binary form. To learn how, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.
 
Yes indeed! I read the Blog on parameters and ADO.net. Here is the result.

SQLstr = "INSERT INTO pohead(pohdponum,pohddte) VALUES(@pohdponum,@pohddte);"
SQLcmd = New MySqlCommand(SQLstr, SQLCnn)
With SQLcmd.Parameters
.AddWithValue("@pohdponum", strPOnum)
.AddWithValue("@pohddte", Date.Now)
End With
SQLcmd.ExecuteNonQuery()
SQLcmd = Nothing

This works and obviously without string:date transformations. And because of such it is cleaner and simpler. And we all know that light switches are more reliable that Space Shuttles. Thanks for the UPDATE!!

Respectfully,
Ideprize
 
Back
Top