This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
It is common in our code to leave DateTime fields initialized to the default value in C#, which is DateTime.MinValue. This allows us to know when a DateTime value is not valid, without having to have the DateTime field for an entity be set to a nullable value. By doing this, we can simplify a lot of business logic code to never have to deal with a null DateTime property, and just check for valid date ranges. And we can easily check if the valid was never initialized by checking against DateTime.MinValue if we really need to know (most of the time we just wan to be able to check a date range, without having to deal with null values). The problem is that this value is outside of the official range that MySQL supports, so when the entity validator kicks in with Lightspeed in bails with the following exception: Mindscape.LightSpeed.Validation.ValidationException was unhandled by user code The problem is I would like to be able to save a special case of DateTime.MinValue to the database, because the MySQL database has support for settings those values to the special DATETIME value of 0 in the database if it is out of range. In order to be able to support this when you read the values back from the database, there is an option in the MySQL Connector/Net to convert the 0 DateTime value to DateTime.MinValue since 0 is not a valid value for DateTime in C#. The option is called 'Convert Zero Datetime' and you can find it documented here: http://dev.mysql.com/doc/refman/5.6/en/connector-net-connection-options.html The section that discuses the DATETIME class and the range is supports is shown below, and it would appear the range is different now as the starting value is lower than the value that Lightspeed is looking for (the supported range is '1000-01-01' to '9999-12-31'). But it also mentions that "Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00 00:00:00' or '0000-00-00')", which is the behaviour we have come to rely on. http://dev.mysql.com/doc/refman/5.6/en/datetime.html Is there some way we can support this in Lightspeed, by allowing the special value of DateTime.MinValue to pass the validation so that it will end up as a zero DATETIME in the database, and will convert back to a DateTime.MinValue value when it is read back in? I have no idea how this would work on other databases, but at the moment we only need to support MySQL :) |
|
|
|
|
Seems like maybe the data provider in lightspeed is limiting the range of dates to what will work across databases, as the year 1753 is the minimum value that SQL Server allows: I suppose that is a valid assumption if you are writing your code to be as database agnostic as possible, but what happens if you are using a legacy MySQL database with data values that might be out of range compared to SQL Server, but perfectly valid in MySQL? I would think the date time range values should probably reflect what each individual database can support, or at least have an option to support that? Yes, it would mean if you wanted to move your database from MySQL to SQL Server, you would have issues, but that would be discovered at the point of trying to migrate the data over. |
|
|
|
|
Thanks for drawing our attention to this. In fact we do respect the database provider when considering the valid range for DateTime values, but it looks like we didn't update the MySQL provider to respect its larger range. I've now remedied this and we will respect MySQL's larger range beginning with the next nightly build. Curiously, although the MySQL spec says that the low end of the MySQL date range is 1 Jan 1000, it actually seems happy to save dates lower than that. We're not sure whether this is a 'nonstandard' feature of MySQL for Windows and other platforms may be limited, but for now we have simply enabled the full .NET date range as this means we don't need to special case MinValue. Let us know if you think we should be validating to 1 Jan 1000 instead of the full range. |
|
|
|
|
I think it is fine to use the entire range because MySQL will convert invalid dates to the zero date. I don't care about dates other than DateTime.MinValue so that will work for me.
|
|
|
|
|
By default DateTime is not nullable because it is a Value Type, using the nullable operator introduced in C# 2, you can achieve this. Using a question mark (?) after the type or using the generic style Nullable. Nullable < DateTime > nullDateTime; or DateTime? nullDateTime = null; More about.....Nullable DateTime Nick |
|
|
|
