5

I have searched around but have yet to find a satisfying answer for my issue:

Overview: I am working on a small app that uses several DateTime fields into a single table using WCF. We are trying to eliminate null fields in all of our tables. Up until now, whenever we are converting a datetime field selected from a table we first verify that it is null before we display or otherwise "use" it. If the value is NULL, we substitute DateTime.MinValue for the value. Since we are now removing the nullable aspect of all fields, we need to insert a common value representing null; since DateTime.MinValue() is substituted everywhere in the code, it seems like a viable value to put into the field as a null substitute.

The problem: Inserting a DateTime.MinValue() causes the generic "problem executing this request" error.

Solution? : As has been documented elsewhere, the DateTime.MinValue() has an unspecified DateTimeKind so... we add the ToUniversalTime call, as in :

DateTime nullDate = DateTime.MinValue.ToUniversalTime(); // 1/1/0001 6:00:00 AM

This doesn't work, perhaps because of some "acceptable range" setting within Date conversions on WCF?

Noted in the comment is the resulting value of "1/1/0001 6:00 am". The odd 6:00am or the year "1" aside, that seems fine. (Considering the past of default years like 12:00 AM 1970 are still fairly standard, but I digress...)

Yes, I know that DateTime is not C#, it's .NET. I'm also aware that WCF has some form of "min" and "max" times allowed within its conversion constraints. I don't know how to set those (nor do I know how to set the VerboseMessages bit; I'm fairly new to WCF.)

The suggestion to manually create a new field value that converts nicely into the table is viable, and it works:

DateTime nullDate = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);

(BTW, If we use the year 1 instead of 1970 (for example) the insert will also fail.)

Even if we were to use the above nullDate as a workable substitute... the problem is that everywhere in the code we are still evaluating against DateTime.MinValue() representing a null date, and those two dates aren't quite the same.

The only viable solution that I can think of is to override or otherwise create an extended class from DateTime to create a common NullDate and modify all the code accordingly.

Does anyone see a good alternative? Does anyone know a solution to inserting the System.DateTime.MinValue() into a WCF table such as altering the acceptable boundries of a good/bad date?

I feel like I'm going to have to bite the bullet and change all references to MinValue... I'm trying to avoid that because it doesn't follow any sort of standard logical thought in evaluating "default" values.

Suggestions anyone?

Picrofo Software
  • 5,475
  • 3
  • 23
  • 37
Steven Powell
  • 115
  • 2
  • 7
  • 4
    I guess it seems to me that the idea of removing nulls from datetime fields is misguided. Substituting a random default value for a field is poor design when the null value more accurately indicates that the field has no value. – Lost in Alabama Nov 28 '12 at 21:21
  • why are you removing null fields from your database!?!? – Daniel A. White Nov 28 '12 at 21:21
  • wcf shouldnt have any problem serializing it. it likely is your database column type. check your service. – Daniel A. White Nov 28 '12 at 21:22
  • 1
    Why oh why would you want to represent "null" any other way other than null?? – myermian Nov 28 '12 at 21:22
  • As GKlesczewski indicates below, using null isn't always viable. It's a similar issue when having an integer value where "0" is actually a valid value - how do you indicate "invalid"? Most use (-1), which when unsigned goes to that type's MAXVAL. There is also my noted issue of displaying (however it looks) null date fields and how to represent them. I inherited the project; think of it as prototype going production. I'm doing what I can with it in the short amount of time before deadline. – Steven Powell Nov 28 '12 at 21:42
  • That being said, I think the best solution has come to light. MaxValue (year 9999) is acceptable to SQL and is also an obviously invalid date. Changing the MinValues to MaxValues is quick. It doesn't address the design issues people ask - that's another topic - but it at least allows me to move forward with a somewhat acceptable solution until an overhaul can be made. – Steven Powell Nov 28 '12 at 21:44

1 Answers1

4

It depends on the database you are using to store the data. SQL Server, for example, has a minimum date of 1/1/1753, see MSDN. I am not sure about Oracle.

If you Have to use a magic date, you can use that, or something else specific (I use 10 December 1815 in honor of the Lady Ada Lovelace, the first programmer). Generally speaking, if you have a situation where you have no known value, the data store should represent the value as 'null.' Now in practice, that's not always viable. What you could do is refactor the nullable columns into a subtable, and only include a child record when you in fact have something to record.

GKlesczewski
  • 192
  • 4
  • Yes, we're using SQL Server. The minimum year value of 1753 would definitely explain the issue. I had been terms of thinking about WCF, not necessarily the server database itself. Thanks. – Steven Powell Nov 28 '12 at 21:37
  • 1
    @StevenPowell If you can change the data type of the column to [DateTime2](http://msdn.microsoft.com/en-us/library/bb677335.aspx) you'll get the same range that is available to the .NET `System.DateTime` object – JG in SD Nov 28 '12 at 21:49
  • @JG - Awesome. Then I don't have to modify all the code. Thanks for the tip. An uninitialized DateTime equates to MinValue, which is why we were using it as a substitute. I've noted a lot of comments elsewhere about "magic values"; I'd love to hear how MS came up with the year 1753 for their "magic value" minimum. DateTime2 allows for more precision - which I don't need right now - but I'm aside from space considerations I don't see why that wouldn't be the standard. – Steven Powell Nov 28 '12 at 22:07
  • @StevenPowell If you want to know why 1/1/1753 was chosen for the min value of SQL DateTime http://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server – JG in SD Nov 28 '12 at 22:24