Hi Everyone:
I am currently looking at an issue involving the best way to handle dates. I am principally using DateTime fields as fields defined in record structures defined within managed VC# code and exposed to COM Interop.
Systems Involved:
- Legacy Native VC++ 6.0
- VC# .NET 2005
- SQL Server 2005.
Issue: What is the best way to handle dates moving between native code, managed code and SQL Server.
Facts:
- All three system have separate and/or different ways of handling dates and times.
- The min value for a date-time field in all three systems is different by-Microsoft-design.
- The highest common denominator for date-time min value is that used by native code. (December 30th, 1899 12:00:00 AM)
- Passing a managed DateTime.MinValue (January 1st, 0001 12:00:00 AM) to SQL Server 2005 is unsupported and throws exception.
Options:
- Have the database set any defined datetime field as default initialized to December 30th, 1899 12:00:00 AM.
- Have managed code convert null database fields by converting null database fields to DateTime of December 30th, 1899 12:00:00 AM before passing DateTime field to native code. Conversely, have native code initialize DATE value fields to DATE = 0 if initialized directly in native code and to DateTime = December 30th, 1899 12:00:00 AM if managed code is called from native code to initialize the field before possibly populating the field.
Developer Opinions are mixed.
- Although .NET's DateTime.MinValue is not supported by SQL Server as a valid date-time, there are benefits to having the .NET attribute DateTime.HasValue which determines if the DateTime field is null or some actual valid date. But in this case, the middle layer of managed code must convert between types. And, DateTime fields outbound initialized from managed code going to native code must be initialized to native min date time; and DateTime fields outbound from managed code going to the database must be initialized to null, given the benefits of DateTime.HasValue.
- Although the DateTime.HasValue would always be true given the database initialized all datetime fields to min datetime under native code specifications, it seems the easiest approach to commonality across all systems The native code's min date-time specification is also supported in managed code and by SQL Server 2005. No conversions in managed code would be necessary. Any initialization of date-time fields in managed code and native code would be identical to the custom default defined in SQL Server 2005.
Any comments are appreciated on the above two options. Any other solution paths are welcome.
James
Dallas, TX
P.S. I do not know what happens at this point if a null DateTime field is passed to native code. Given this field is not initialized to a DateTime value, I presume some exception might be thrown. I also do not know the effects of a negative value for a DATE structure in native code; I assume this is not supported.
[Project Extract - VC# Managed Code]
#region
Design considerations for handling Date-Time fields /// <remarks> /// ====================================================================================================== /// Design considerations for handling Date-Time fields between native code, managed code and database. /// ====================================================================================================== /// ------------------------------------------------------------------------------------------------------ /// Microsoft Specifications for Native VC++ code, Managed VC# .NET code, and SQL Server database /// ------------------------------------------------------------------------------------------------------ /// Native code supports a min date of December 30th, 1899 12:00:00 AM. [ie. (DATE)0 or (double)0)] /// Managed code supports a min date of January 1st, 0001 12:00:00 AM. [ie. DateTime.MinValue] /// SQL Server supports dates between January 1st, 1753 12:00:00 AM and December 31st, 9999 11:59:59 PM. /// ------------------------------------------------------------------------------------------------------ /// Analysis/Effects on Managed Code and Database/// ------------------------------------------------------------------------------------------------------
/// If the database receives DateTime.MinValue in managed code from an insert or update to the database, /// an exception is thrown; the native min date-time of "(DATE)0" is okay. Therefore, dates to and from /// native client must support this range and be initialized. A null is not supported by-design from native /// code; however, a null is permitted in managed code and the database. /// Any DateTime fields returned to native code cannot be defined as null. /// ------------------------------------------------------------------------------------------------------ /// Design/Implementation for Managed Code and recommendations for database. /// ------------------------------------------------------------------------------------------------------ /// Best course likely here is to initialize DateTime typed fields to native min date-time specification. /// Conversely, the database design, in my opinion, would be improved by setting all default date-time /// fields to December 30th, 1899 12:00:00 AM, the min date-time specification for native code rather than /// using a null or even using the min date-time for SQL Server of January 1st, 1753 12:00:00 AM. /// ------------------------------------------------------------------------------------------------------ /// Summary of options for handling managed code implementation of date-time fields /// ------------------------------------------------------------------------------------------------------ /// Option 1: Initialize date-time fields to null, the default in SQL Server 2005 database. (requires conversions) /// Option 2: Initialize date-time fields to DateTime.MinValue, January 1st, 0001 12:00:00 AM. (triggers database exception) /// Option 3: Initialize date-time fields to native min date-time, December 30th, 1899 12:00:00 AM. /// </remarks>#endregion
// Design considerations for handling Date-Time fields //record.m_dtStartTime = null; //record.m_dtAbandonTime = null; //record.m_dtStartTime = DateTime.MinValue; //record.m_dtAbandonTime = DateTime.MinValue; int year = 1899, month = 12, day = 30, hour = 0, minute = 0, second = 0;record.m_dtStartTime =
new DateTime(year, month, day, hour, minute, second);[/Project Extract - VC# Managed Code]
DateTime Support Native to Managed to SQLServer with COM Interop
Flame Thrower
To the interested: I edited and fixed some typos in the solution object, but the code in my posted builds clean now.
Also, I would like to add some comments to my previously submitted code above.
James Sigler 10-20-2006
Dallas, TX
jmsigler2@hotmail.com
ekkapop
Hi PSHK:
I created a solution to this issue. I unfortunately found that deriving from DateTime is not possible or restricted; in other words, the class is sealed. So, there is no way to create a NativeDateTime or SqlServerDateTime where the supported DateTime.MinValue and DateTime.MaxValue, public read-only attributes, can be updated in the constructor to those supported in each respective environment. If you ask me, Microsoft should remove this restriction. I did succeed in created conversion routines as you will see below. This solution object is likely something that many developers can use.
Thanks again for your input.
James Sigler, 10-19-2006
Dallas, TX
jmsigler2@hotmail.com
[Solution Object]
using
System;using
System.Collections.Generic;using
System.Text;namespace
MySystem.Helper{
public static class Convert{
#region
Properties for Native DateTime MinValue // Supported by native COleDateTime which encapsulate the DATE native data type used in OLE automation. // COleDateTime min value is January 1st, 100, 12:00:00 AM. private static int _nativeDateTimeMinValueYear = 100; private static int _nativeDateTimeMinValueMonth = 1; private static int _nativeDateTimeMinValueDay = 1; private static int _nativeDateTimeMinValueHour = 0; private static int _nativeDateTimeMinValueMinute = 0; private static int _nativeDateTimeMinValueSecond = 0; private static DateTime _nativeDateTimeMinValue = new DateTime(_nativeDateTimeMinValueYear, _nativeDateTimeMinValueMonth,_nativeDateTimeMinValueDay, _nativeDateTimeMinValueHour, _nativeDateTimeMinValueMinute, _nativeDateTimeMinValueSecond,
DateTimeKind.Local); public static DateTime NativeDateTimeMinValue{
get { return _nativeDateTimeMinValue; }}
#endregion
// Properties for Native DateTime MinValue#region
Properties for Native DateTime NullValue // Supported by native COleDateTime which encapsulate the DATE native data type used in native code and OLE automation. // COleDateTime where encapsulted DATE is equal to zero. The min value is equivalent to December 30th, 1899, 12:00:00 AM. private static int _nativeDateTimeNullValueYear = 1899; private static int _nativeDateTimeNullValueMonth = 12; private static int _nativeDateTimeNullValueDay = 30; private static int _nativeDateTimeNullValueHour = 0; private static int _nativeDateTimeNullValueMinute = 0; private static int _nativeDateTimeNullValueSecond = 0; private static DateTime _nativeDateTimeNullValue = new DateTime(_nativeDateTimeNullValueYear, _nativeDateTimeNullValueMonth,_nativeDateTimeNullValueDay, _nativeDateTimeNullValueHour, _nativeDateTimeNullValueMinute, _nativeDateTimeNullValueSecond,
DateTimeKind.Local); public static DateTime NativeDateTimeNullValue{
get { return _nativeDateTimeNullValue; }}
#endregion
// Properties for Native DateTime NullValue#region
Properties for SQL Server DateTime MinValue // Supported by SQL Server 2005 database. The min value is equivalent to January 1st, 1753, 12:00:00 AM private static int _sqlserverDateTimeMinValueYear = 100; private static int _sqlserverDateTimeMinValueMonth = 1; private static int _sqlserverDateTimeMinValueDay = 1; private static int _sqlserverDateTimeMinValueHour = 0; private static int _sqlserverDateTimeMinValueMinute = 0; private static int _sqlserverDateTimeMinValueSecond = 0; private static DateTime _sqlserverDateTimeMinValue = new DateTime(_sqlserverDateTimeMinValueYear, _sqlserverDateTimeMinValueMonth,_sqlserverDateTimeMinValueDay, _sqlserverDateTimeMinValueHour, _sqlserverDateTimeMinValueMinute, _sqlserverDateTimeMinValueSecond,
DateTimeKind.Local); public static DateTime SqlServerDateTimeMinValue{
get { return _sqlserverDateTimeMinValue; }}
#endregion
// Properties for SQL Server DateTime MinValue#region
Properties for Standard DateTime MaxValue // Commonly supported by SQL Server 2005 database, .NET 2005, and VC++ native code. // The standard max value is equivalent to December 31st, 9999, 11:59:59 AM. private static int _stdDateTimeMaxValueYear = 9999; private static int _stdDateTimeMaxValueMonth = 12; private static int _stdDateTimeMaxValueDay = 31; private static int _stdDateTimeMaxValueHour = 11; private static int _stdDateTimeMaxValueMinute = 59; private static int _stdDateTimeMaxValueSecond = 59; private static DateTime _stdDateTimeMaxValue = new DateTime(_stdDateTimeMaxValueYear, _stdDateTimeMaxValueMonth,_stdDateTimeMaxValueDay, _stdDateTimeMaxValueHour, _stdDateTimeMaxValueMinute, _stdDateTimeMaxValueSecond,
DateTimeKind.Local); public static DateTime StdDateTimeMaxValue{
get { return _stdDateTimeMaxValue; }}
#endregion
// Properties for Standard DateTime MaxValue#region
Members public static DateTime ToSQLServerUTCDateTime(DateTime value){
if ((false == value.HasValue)|| (System.
Convert.ToDateTime(value) == _nativeDateTimeNullValue)){
return null;}
else{
if ((System.Convert.ToDateTime(value).Kind == DateTimeKind.Local)|| (System.
Convert.ToDateTime(value).Kind == DateTimeKind.Unspecified)){
value = System.
Convert.ToDateTime(value).ToUniversalTime();}
// Test DateTime value in UTC format to see if it falls outside the valid range allowed for SQL Server 2005. if ((System.Convert.ToDateTime(value) < _sqlserverDateTimeMinValue)|| (System.
Convert.ToDateTime(value) > _stdDateTimeMaxValue)){
throw new Exception("SqlDateTime is out-of-range and not supported by SQL Server 2005. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.");}
return value;}
}
public static DateTime ToNativeLocalDateTime(DateTime value){
if (false == value.HasValue) // or (null == value){
// By-Design, a database or VC# value of null will be assigned: // a COleDateTime value of "December 30th, 1899, 12:00:00 AM" or a DATE = (double)0.0 in native code. // Note: In native code, a COleDateTime date-time variable encapsulates use of a DATE structure. return _nativeDateTimeNullValue;}
else{
if ((System.Convert.ToDateTime(value).Kind == DateTimeKind.Utc)|| (System.
Convert.ToDateTime(value).Kind == DateTimeKind.Unspecified)){
value = System.
Convert.ToDateTime(value).ToLocalTime();}
// Test DateTime value in local time format to see if it falls outside the valid range allowed for native code. if ((System.Convert.ToDateTime(value) < _nativeDateTimeMinValue)|| (System.
Convert.ToDateTime(value) > _stdDateTimeMaxValue)){
throw new Exception("Native-Local date-time (COleDateTime/DATE) is out-of-range and not supported by OLE automation. Must be between 1/1/100 12:00:00 AM and 12/31/9999 11:59:59 PM.");}
return System.Convert.ToDateTime(value);}
}
#endregion
}
}
[/Solution Object]// eof
jomon
PSHK:
I like your idea of deriving from the DateTime class.
First, I can then create classes like the following:
or
I can then override the public readonly properties for MinValue and MaxValue in the derived class constructor.
Secondly, I need to think more about conversions. Conversion of datatypes occurs primarily in static class System.Convert. I had wondered if I could extend System.Convert for conversion sake; I found that this is not possible and produces an error in attempting to derive from a static class. So, I will create a class Convert in my own project namespace, or do as you suggested and just handle the conversion intrinsically within the newly created SQLServerDateTime and NativeDateTime classes.
Then, for example, I would define and implement the following:
// This would be used for field values received through COM Interop from Native Code.
// A native min date-time would be converted to null before going to the database.
public static DateTime ToSQLServerDateTime(DateTime value);
// This would be used to for field values received from the database.
// A null database value would be converted to a native min datetime value before going to native code.
public static NativeDateTime ToNativeDateTime(DateTime sqlsvrValue);
Some validation handling and exception handling could be used in these conversion defined methods. For example, an exception could be thrown if attempting to convert a DateTime value to a NativeDateTime value which is less that the supported min value for native code.
Last, I will have to update my record structures exposed to COM Interop in managed code from DateTime to NativeDateTime. The great part here is that I now have a managed DateTime object that can act with the range constraints as is by-design specified for native code.
It is unfortunate that all of the Microsoft groups did not collaborate in designing all of these systems to have the same date. However, this idea looks good enough to be a Microsoft solution object for this case.
Microsoft should consider adding these extended DateTime conversion routines and extended DateTime managed types to the framework in System.Convert as a means of handling DateTime issues between Microsoft systems.
I want to note that your collective idea in using one common extended DateTime class such as MyProjectDateTime shown above and handling all conversions locally within that one single class is just as valid a solution.
Thank you very much for your input. I think it sparked an excellent solution.
James
Dallas, TX
jmsigler2@hotmail.com
CobaltD
Hi,
I don't know if I understand your problem well enough. But actually I think, what if you derive your own DateTime class from the .Net DateTime class, so that you can decide your own min date, and at the same time simplify the coding and management This derived class will also handles data conversion when case are necessary.
If I were you, maybe I will just set my min date to 01/01/1970. Then the three system live happily with these bridge pattern.