How to use IOM provider to read Date values into a .NET application in an efficient way

[ReidarDMidtun]

I am using the IOM provider to read data from a SAS Base library into a .NET C# application. The problem arises when reading columns defined as Date values. When reading unformatted objects, the date values are returned as double floating point numbers. I need a function to transform the number to a .NET DateTime object.

I know I can instrument the IOM provider to return formatted string objects by setting

      ADODB.Recordset.Properties["SAS Formats"] = "_ALL_";

If I use this to have formatted objects returned, there is a performance degrade, it runs 100 times slower. Due to this I was hoping I could used unformatted objects and transform the number to DateTime objects in the .NET world. Is there a .NET library with SAS date functions that can do this?

See case [SAS 7613337300] for more info.

[ChrisHemedinger · Accepted solution]

You might need to do this transformation yourself.  Here's an example for going the other way (Microsoft datetime to SAS).

[ReidarDMidtun]

Once I got the recipe, making a transform from a SAS date value was easy. 

A SAS datetime value is: The number of seconds since 12:00 midnight on January 1, 1960.

This value has to be mapped to a .NET DateTime value. This value is the number of ticks since 12:00 midnight, January 1, 0001 A.D. (C.E.) in the Gregorian Calendar. A tick is 100 nanoseconds.

Here is the transform function written in C#:

   private DateTime SASBaseDate = new DateTime(1960, 01, 01);
   private const long TicksInASecond = 10000000;
   private DateTime ToDate(object sasTics) {
      if (sasTics == null || sasTics is DBNull)
         return DateTime.MinValue;
       var lTicks = Convert.ToInt64(sasTics);
       return new DateTime(SASBaseDate.Ticks + (lTicks * TicksInASecond));
   }