DateTime – It’s all about the rules!

The Rules

  1. Calculations and comparisons of DateTime instances are only meaningful when the instances being compared or used are representations of points in time from the same time-zone perspective.
  2. A developer is responsible for keeping track of time-zone information associated with a DateTime value via some external mechanism. Typically this is accomplished by defining another field or variable that you use to record time-zone information when you store a DateTime value type. This approach (storing the time-zone sense alongside the DateTime value) is the most accurate and allows different developers at different points in a program’s lifecycle to always have a clear understanding of the meaning of a DateTime value. Another common approach is to make it a “rule” in your design that all time values are stored in a specific time-zone context. This approach does not require additional storage to save a user’s view of the time-zone context, but introduces the risk that a time value will be misinterpreted or stored incorrectly down the road by a developer that isn’t aware of the rule.

Continue reading “DateTime – It’s all about the rules!”

Custom CLRType to SqlDbType Mapping

Right now I’m working on implementing our own ‘LocalDate’ object because the built in ‘DateTime’  is causing several issues for us – it has a time component, Mongo DB automatically converts it to Utc when you save. All of that prompted us to come up with a custom object ‘LocalDate’.

The first requirement is that it’s recognised and saved correctly by Mongo DB, which was a relatively painless process. Then I had to make it behave similarly to the ‘DateTime’ object – create some explicit cast functions, implement various operators etc. Then followed binary and xml serialisation – so far, easy.

The problem arose when I tried to write a unit test to save it to SQL Db and I immediately got the following exception:
“No mapping exists from object type LocalDate to a known managed provider native type.”

I thought, fair enough, ADO.NET doesn’t know what to do with the type, so I must somehow specify how to convert it to a SqlDbType.

The problem is that the mapping is hardcoded and cannot be changed, unless I come up with some hacky solution.

So, for the time being the users have to call ‘ToSqlDbType()’ before saving it to SQL.

The code below is responsible for the problem. As you can see there is no easy solution to this 😦

 static private MetaType GetMetaTypeFromValue(Type dataType, object value, bool inferLen, bool streamAllowed) {
            switch (Type.GetTypeCode(dataType)) {
                case TypeCode.Empty:     throw ADP.InvalidDataType(TypeCode.Empty);
                case TypeCode.Object:
                    if (dataType == typeof(System.Byte[])) {
                        // mdac 90455 must not default to image if inferLen is false ...
                        if (!inferLen || ((byte[]) value).Length <= TdsEnums.TYPE_SIZE_LIMIT) {
                            return MetaVarBinary;
                        else {
                            return MetaImage;
                    else if (dataType == typeof(System.Guid)) {
                        return MetaUniqueId;
                    else if (dataType == typeof(System.Object)) {
                        return MetaVariant;
                    } // check sql types now
                    else if (dataType == typeof(SqlBinary))
                        return MetaVarBinary;
                    else if (dataType == typeof(SqlBoolean))
                        return MetaBit;
                    else if (dataType == typeof(SqlByte))
                        return MetaTinyInt;
                    else if (dataType == typeof(SqlBytes))
                        return MetaVarBinary;
                    else if (dataType == typeof(SqlChars))
                        return  MetaNVarChar; // MDAC 87587
                    else if (dataType == typeof(SqlDateTime))
                        return MetaDateTime;
                    else if (dataType == typeof(SqlDouble))
                        return MetaFloat;
                    else if (dataType == typeof(SqlGuid))
                        return MetaUniqueId;
                    else if (dataType == typeof(SqlInt16))
                        return MetaSmallInt;
                    else if (dataType == typeof(SqlInt32))
                        return MetaInt;
                    else if (dataType == typeof(SqlInt64))
                        return MetaBigInt;
                    else if (dataType == typeof(SqlMoney))
                        return MetaMoney;
                    else if (dataType == typeof(SqlDecimal))
                        return MetaDecimal;
                    else if (dataType == typeof(SqlSingle))
                        return MetaReal;
                    else if (dataType == typeof(SqlXml))
                        return MetaXml;                
                    else if (dataType == typeof(SqlString)) {
                        return ((inferLen && !((SqlString)value).IsNull) ? PromoteStringType(((SqlString)value).Value) : MetaNVarChar); // MDAC 87587
                    else if (dataType == typeof(IEnumerable<DbDataRecord>) || dataType == typeof(DataTable)) {
                        return MetaTable;
                    } else if (dataType == typeof(TimeSpan)) {
                        return MetaTime;
                    else if (dataType == typeof(DateTimeOffset)) {
                        return MetaDateTimeOffset;
                    else { 
                        // UDT ?
                        SqlUdtInfo attribs = SqlUdtInfo.TryGetFromType(dataType);
                        if (attribs != null) {
                            return MetaUdt;
                        if (streamAllowed) {
                            // Derived from Stream ?
                            if (typeof(Stream).IsAssignableFrom(dataType)) {
                                return MetaVarBinary;
                            // Derived from TextReader ?
                            if (typeof(TextReader).IsAssignableFrom(dataType)) {
                                return MetaNVarChar;
                            // Derived from XmlReader ? 
                            if (typeof(System.Xml.XmlReader).IsAssignableFrom(dataType)) {
                                return MetaXml;
                    throw ADP.UnknownDataType(dataType);

                case TypeCode.DBNull:    throw ADP.InvalidDataType(TypeCode.DBNull);
                case TypeCode.Boolean:   return MetaBit;
                case TypeCode.Char:      throw ADP.InvalidDataType(TypeCode.Char);
                case TypeCode.SByte:     throw ADP.InvalidDataType(TypeCode.SByte);
                case TypeCode.Byte:      return MetaTinyInt;
                case TypeCode.Int16:     return MetaSmallInt;
                case TypeCode.UInt16:    throw ADP.InvalidDataType(TypeCode.UInt16);
                case TypeCode.Int32:     return MetaInt;
                case TypeCode.UInt32:    throw ADP.InvalidDataType(TypeCode.UInt32);
                case TypeCode.Int64:     return MetaBigInt;
                case TypeCode.UInt64:    throw ADP.InvalidDataType(TypeCode.UInt64);
                case TypeCode.Single:    return MetaReal;
                case TypeCode.Double:    return MetaFloat;
                case TypeCode.Decimal:   return MetaDecimal;
                case TypeCode.DateTime:  return MetaDateTime;
                case TypeCode.String:    return (inferLen ? PromoteStringType((string)value) : MetaNVarChar);
                default:                 throw ADP.UnknownDataTypeCode(dataType, Type.GetTypeCode(dataType));

Mongo – DateTime Issue

There is an issue with Mongo DB (it is an issue if you don’t need that feature). Whenever you pass a date in an entity to a Mongo DB server in a different timezone it will automatically adjust the DateTime value of the object property.

The problem is very easy to solve by decorating the DateTime property with

[BsonDateTimeOptions(Kind = DateTimeKind.Local)]
public DateTime TradeTime { get; set; }

where you do need the time component or

[BsonDateTimeOptions(DateOnly = true)]
public DateTime ReportingDate { get; set; }

when you only care about the date.

An even better solution is to set a global setting of the C# driver:

BsonSerializer.RegisterSerializer(typeof(DateTime), new DateTimeSerializer(DateTimeSerializationOption(DateTimeSerializationOptions.LocalInstance));

Blog at

Up ↑