No datetime values populated from MySQL using SubSonic 3 Linq

Question

I have a MySQL table with a couple of Datetime columns. The columns are set to allow null and some have default value '0000-00-00 00:00:00'. This is a conversion project from ASP to ASP.NET so the table is full of data, and where some rows still have the default value, so I had to set "Allow Zero Datetime=True" in the connectionstring to avoid the exception "Unable to convert MySQL date/time value to System.DateTime"

Now when I generate the code it all works fine and I get properties of type DateTime? for those columns, but when I query the database and populate an object representing the table all DateTime properties are null. Other properties gets populated their correct values.

Anybody knows why?

I'm using MySQL Connector 6.1.3 and SubSonic.Core compiled from the github today (11/17/2009)

Answer 1

My trick for converting datetime format from different SQL DBMS is to load the column as VARCHAR. Then use string functions such as SUBSTRING and CONCAT to play around and get the desire format. From experience this saves a lot of time. No need to worry about dbms automatic conversion for datetime.

MySQL uses 'YYYY-MM-DD HH:MM:SS'

Answer 2

I did some data cleaning. Updated all datetime columns to null where date was '0000-00...' and removed "Allow Zero Datetime=True" from the connectionstring, and then it works. Guess zero dates are not supported by SubSonic, and why should it, I don´t see any use for zero dates over null.