Can't convert MySqlDateTime to System.Date - not null

Question

0.00/5 (No votes)

See more:

Am using MySqlData (Connector/Net) with a MySql database and VB.NET

The following

VB

Dim objConn As New MySqlConnection
Dim objCmd As New MySqlCommand
Dim objDA As New MySqlDataAdapter
Dim ds As DataSet
Dim sb As New StringBuilder
Dim d As Date
Try
    objConn.ConnectionString = "server=localhost;database=xxx;uid=xxx;pwd=xxx;AllowZeroDateTime=True;ConvertZeroDateTime=True"
    objConn.Open()
    ds = New DataSet("MyDataSet")
    objDA.SelectCommand = New MySqlCommand
    objDA.SelectCommand.Connection = objConn
    objDA.SelectCommand.CommandText = "select datecolumn from datatable where ID = 1"
    objDA.Fill(ds, "tbl")
    objConn.Close()
    d = CDate(ds.Tables("tbl").Rows(0)("datecolumn"))
    sb.Append(String.Format("{0:dd-MMM-yyyy}", d))
Catch ex As Exception
    If objConn.State = ConnectionState.Open Then objConn.Close()
    sb.Append(ex.Message)
End Try

yields the error

XML

Conversion from type 'MySqlDateTime' to type 'Date' is not valid.

NB - the datecolumn value is NOT a null value, but a valid date/time value.
WTF is going on? Every time I try to google this all I get are answers relating to handling NULL values. How can I work with this MySqlDateTime in my code? i.e. convet it to a System.Date value?

Posted 1-Aug-15 4:25am

Wombaticus

Updated 1-Aug-15 4:27am

v2

Add a Solution

Comments

Wendelius 1-Aug-15 10:32am

If using debugger you look at the ds.Tables("tbl").Rows(0)("datecolumn") what is the content you see and what is the data type shown by the debugger?

Wombaticus 1-Aug-15 10:43am

I'm actually running this in an ASP.NET page (Sorry, bad tag to the Q)
If I add a line
sb.Append(ds.Tables("tbl").Rows(0)("datecolumn").GetType.ToString & vbCrLf)
It writes out MySql.Data.Types.MySqlDateTime
Getting the value is the problem...
As a workaround I can amend the SQL to
select DATE_FORMAT(datecolumn, '%d-%M-%Y %H:%i:%S') as datecolumn from datatable where ID = 1
and this works, but I would prefer not to have to go round altering all my SQL statements.... besides, it's a bit wasteful converting dates to strings and back again.

PIEBALDconsult 1-Aug-15 11:03am

MySQL supports a special "zero date" (0000-00-00), see whether or not you're running into one.

Wombaticus 1-Aug-15 11:06am

No it's nowt to do with that, ta

2 solutions

Solution 1

You're probably having the same problem as here[^]

Posted 1-Aug-15 4:43am

Wendelius

Comments

Wombaticus 1-Aug-15 10:47am

Ta, but I found that page already and it doesn't help

Wendelius 1-Aug-15 10:56am

Since the default format of the MySqlDateTime cannot be changed (as far as I know) then I think one way is to create your own method for the conversion.

Unless CDate(ds.Tables("tbl").Rows(0)("datecolumn").ToString) works, first find out what the content is if you cast it to string, use for example

sb.Append(ds.Tables("tbl").Rows(0)("datecolumn").ToString)

and then based on that create for example global method for converting the text to DateTime using for example ParseExact[^]

Wombaticus 1-Aug-15 11:04am

sb.Append(ds.Tables("tbl").Rows(0)("datecolumn").ToString)
gives
01/08/2015 14:23:29
and then
d = CDate(ds.Tables("tbl").Rows(0)("datecolumn"))
gives the error....
As mentioned above, I can "solve" this by amending my SQL to
select DATE_FORMAT(datecolumn, '%d-%M-%Y %H:%i:%S') as datecolumn from datatable where ID = 1
but this seems very wasteful, converting to a string and then back again to a date (in code)

Wendelius 1-Aug-15 11:47am

As I wrote, try

CDate(ds.Tables("tbl").Rows(0)("datecolumn").ToString)

But it also seems that you already used Parse to do the same thing.

Wombaticus 1-Aug-15 11:50am

Yeah - mind you, it still seems a bit wasteful to have to convert a date to a string and then bsck to a date again. Things like
If IsDate(ds.Tables("tbl").Rows(0)("datecolumn")) Then...
will fail for valid dates, and
ANd just trying to assign
ds.Tables("tbl").Rows(0)("datecolumn")
to a date variable gives the error without first jumping through this hoop of converting to a string and back again....

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Accepted Answer · 2015-08-01T05:26:00

Solution 2

try

VB

Dim d2 As DateTime = DateTime.Parse(ds.Tables("tbl").Rows(0)("datecolumn").ToString())
sb.Append(String.Format("{0:dd-MMM-yyyy}", d2))

Posted 1-Aug-15 5:26am

DamithSL

Updated 1-Aug-15 5:36am

v2

Comments

Wombaticus 1-Aug-15 11:30am

still gives the same error
Conversion from type 'MySqlDateTime' to type 'String' is not valid.

DamithSL 1-Aug-15 11:38am

one more change try with .ToString() before calling DateTime.Parse

Wombaticus 1-Aug-15 11:42am

Ah, YES! Well done - that works :)

DamithSL 1-Aug-15 11:45am

I'm glad I was able to help