|
Replies:
11
-
Pages:
1
-
Last Post:
Aug 19, 2011 10:32 AM
by: Mauritz
|
|
|
|
|
|
|
Unable to convert MySQL date/time value to System.DateTime [1 Attachment]
Posted:
Dec 1, 2010 10:46 AM
|
|
|
<*>[Attachment(s) from Scott Sanders included below]
I viewed the data of a table in an existing MySQL DB where mdate looks like
11/10/2009 9:50:49 AM.
|
|
|
Posts:
1
Registered:
12/2/10
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime [1 Attachment]
Posted:
Dec 2, 2010 7:32 AM
in response to: Scott Sanders
|
|
|
I get the same issue here - but it only happends where date fields have null values.
Previous version of Toad had no trouble with this, though; I keep 4.6 around for this reason. 4.6 just seems to substitute 1/1/0001 to display null value dates in the grid.
|
|
|
Posts:
3
Registered:
10/12/10
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime [1 Attachment]
Posted:
Dec 8, 2010 2:09 AM
in response to: Arkayn
|
|
|
I had my PC re-masterized by IT services and had the same issue after installing the new version of Toad for MySQL. I confirm that apparently, the tool is sticking much more with the table definition which makes this kind of errors appear when you have "NOT NULL" columns with "NULL" values.
I truncated my table, changed the definition of all my dates where I know there could have null dates and re-imported the data again.
Since then, no more issues with Toad!
Also, I'd like to add that now the "Import" tool is working just fine (with a huge CSV file of 150Mb) while it was always crashing the software at the end with the previous version ;)
Hoping this will help somebody...
Tanhys
|
Tanhys
|
|
Posts:
5
Registered:
11/18/10
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime [1 Attachment]
Posted:
Dec 10, 2010 4:20 AM
in response to: Tanhys
|
|
|
I'm not too sure that the columns are literally NULL, as in the MySQL standard representation of NULL, but they are tantamount.
In the MySQL client, the problem rows had the TS of 0000-00-00 00:00:00.
I'm not sure if something lime 2010-00-00 is allowed.
The problem is Toad is just tossing the date part straight into a standard calendar LIB.
That's not going to work for a month/day of 00 because you don't even need to do a Gregorian Calendar check to validate that. A month cannot be Zero.
The year of 0000 may fail because usually computer dates don't like to go lower than 1970 (By the Silicon Calendar it is currently the year 40).
The obvious solution, is that it should behave like it would a column that allows NULLs as you have pointed out. That is, you can set to a calendar date, or set to a blank date of 0000-00-00 00:00:00.
If 2010-00-01 is allow or similar, then the calendar would want to be able to represent that with and extra special month of "??" in my opinion, which would potentially be better than 00 as 00 may confuse people into thinking that's where the month offset starts from (ending at 11).
Even if the calendar API doesn't allow you to set some dates like that, Toad must be able to display those columns accurately.
|
|
|
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime
Posted:
Jan 3, 2011 2:17 PM
in response to: Scott Sanders
|
|
|
Any news on this one yet?
I still have to use 4.6 next to 5.0
Awfully quiet in this group lately, btw.
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime
Posted:
Jan 20, 2011 10:57 AM
in response to: mijnjahoegroepsid
|
|
|
Sorry for taking so long to get to this. This is resolved in the new Toad for MySQL 6.0 beta to be released shortly.
|
|
|
Posts:
1
Registered:
5/24/11
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime
Posted:
May 24, 2011 7:34 AM
in response to: Mauritz
|
|
|
I actually was using the Beta version (6.0) because of this and was very happy. But now after using the beta for so long it says something to the effect the 'Beta period is expired' and it sends me to download 5.0 again. I have never seen a beta that expired ... When is this beta going to be GA so I don't have to deal with 5.0 anymore (too many bugs)
|
|
|
Posts:
40
Registered:
1/27/11
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime
Posted:
May 24, 2011 12:03 PM
in response to: giovanMusk
|
|
|
The latest beta works. They expire periodically.
|
|
|
Posts:
40
Registered:
1/27/11
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime
Posted:
Aug 18, 2011 2:09 PM
in response to: Scott M. Sanders
|
|
|
Actually the latest beta does not really solve the problem -- it just moves all these errors to the hidden output tab at the bottom.
My guess is that it has issues with blank values for dates -- and I read somewhere that putting Allow Zero Datetime=true in the connection string may fix it.
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime
Posted:
Aug 18, 2011 2:42 PM
in response to: Scott M. Sanders
|
|
|
Yes, that is what we are doing however since this does actually change the value you see in the grid a warning that this has happened whenever we read one of these values (That is what you are seeing in the output tab) so this is the expected behavior.
|
|
|
Posts:
40
Registered:
1/27/11
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime
Posted:
Aug 19, 2011 6:07 AM
in response to: Mauritz
|
|
|
It changes the display of the value -- to blank, like other valid cells -- and does not indicate a problem, nor tries to fix it, nor shows in the output which cells or rows these may be, just their column names.
Contrarily, SQuirreL at least shows these cells as <Error>, but it does not let me fix them -- so presently, I am finding all of these <Error> cells in SQuirreL, finding them again in Toad by ID, editing a random cell in that row with Toad, and editing it back with Toad, so the cells are just nullified, as they are causing issues elsewhere.
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: Unable to convert MySQL date/time value to System.DateTime
Posted:
Aug 19, 2011 10:32 AM
in response to: Scott M. Sanders
|
|
|
Unfortunately the component we use to access MySQL does not allow for this.
It's easy to find them with a query though:
select * from table where datecolumn = '0000-00-00 00:00:00'
|
|
|
|
Legend
|
|
Guru: 2001
+
pts
|
|
Expert: 751
- 2000
pts
|
|
Enthusiast: 31
- 750
pts
|
|
Novice: 0
- 30
pts
|
|
Moderators
|
|
Helpful answer
(5 pts)
|
|
Answered
(10 pts)
|
|
