Converting a .NET Datetime to a DateTime2 in T-SQL

This article presents a handy T-Sql that extracts a DateTime value stored in .NET in a numerical field and converts it into a Sql Server DateTime (DateTime2 column). The T-SQL will convert into a DateTime2 with a SECOND precision. An assumption here is that any numerical value larger than 100000000000 contains a DateTime value. This is an acceptable assumption when you log data, as very large values usually indicate a datetime value. But you might want to have additional checking here of course in addition to what i show in the example T-SQL script. Here is the T-SQL that shows how we can convert the .NET DateTime into a SQL DateTime.

-- Last updated: March 18, 2025
-- Synopsis: This script retrieves detailed change log information from the ObjectChanges, PropertyChanges, and ChangeSets tables.
-- It filters the results based on specific identifiers stored in a table variable, in this example Guids. 

-- In this example T-Sql the library FrameLog is used to store a log

-- DateTime columns are retrieved by looking at number of ticks elapsed since DateTime.MinValue as 
-- DateTime columns are stored in SQL Server as a this numeric value. 


DECLARE @EXAMPLEGUIDS TABLE (ID NVARCHAR(36))

INSERT INTO @EXAMPLEGUIDS (Id)
VALUES
('1968126a-64c1-4d15-bf23-8cb8497dcaa9'), 
('3e11aad8-95df-4377-ad63-c2fec3d43034'),
('acbdd116-b6a5-4425-907b-f86cb55aeedd') --tip: define which form Guids to fetch the ChangeLog database tables which 'FrameLog' uses The form Guids can each be retrieved from url showing the form in MRS in the browser

SELECT 
o.Id as ObjectChanges_Id, 
o.ObjectReference as ObjectReference, 
o.TypeName as ObjectChanges_TypeName, 
c.Id as Changeset_Id, 
c.[Timestamp] as Changeset_Timestamp,
c.Author_UserName as Changeset_AuthorName,
p.[Id] as PropertyChanges_Id,
p.[PropertyName],
p.[Value],
p.[ValueAsInt],
CASE 
    WHEN p.Value IS NOT NULL 
         AND ISNUMERIC(p.[Value]) = 1 
         AND CAST(p.[Value] AS decimal) > 100000000000 
    THEN 
        DATEADD(SECOND, 
            CAST(CAST(p.[Value] AS decimal) / 10000000 AS BIGINT) % 60, 
            DATEADD(MINUTE, 
                CAST(CAST(p.[Value] AS decimal) / 10000000 / 60 AS BIGINT), 
                CAST('0001-01-01' AS datetime2)
            )
        )
    ELSE NULL
END AS ValueAsDate,
o.ChangeType as ObjectChanges_ChangeTypeIfSet
FROM propertychanges p
LEFT OUTER JOIN ObjectChanges o on o.Id = p.ObjectChange_Id
LEFT OUTER JOIN ChangeSets c on o.ChangeSet_Id = c.Id
WHERE ObjectChange_Id in (
 SELECT ObjectChanges.Id
  FROM PropertyChanges
  LEFT OUTER JOIN ObjectChanges on ObjectChanges.Id = PropertyChanges.ObjectChange_Id
  LEFT OUTER JOIN ChangeSets on ObjectChanges.ChangeSet_Id = ChangeSets.Id
  WHERE ObjectChange_Id in (SELECT Id FROM ObjectChanges where ObjectReference IN (
   SELECT Id FROM @EXAMPLEGUIDS
   ))) --find out the Changeset where ObjectChange_Id equals the Id of ObjectChanges where ObjectReference equals one of the identifiers in @EXAMPLEGUIDS
ORDER BY ObjectReference, Changeset_Id DESC, Changeset_Timestamp DESC

The T-Sql is handy in case you come across datetime columns from .NET that are saved as ticks in a column as numerical value and shows how we can do the conversion.

Overcoming limitations in Contains in Entity Framework

Entity Framework will hit a performance penalty bottleneck or crash if Contains contains a too large of a list. Here is how you can avoid this, using Marc Gravell's excellent approach in this. I am including some tests of this. I also suggest you consider LinqKit to use expandable queries to make this all work. First off, this class contains the extension methods for Entity Framework for this:

 
 
 public class EntityExtensions {
 
 /// <summary>
        /// This method overcomes a weakness with Entity Framework with Contains where you can partition the values to look for into 
        /// blocks or partitions, it is modeled after Marc Gravell's answer here:
        /// https://stackoverflow.com/a/568771/741368
        /// Entity Framework hits a limit of 2100 parameter limit in the DB but probably comes into trouble before this limit as even
        /// queries with several 100 parameters are slow.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="TValue"></typeparam>
        /// <param name="source">Source, for example DbSet (table)</param>
        /// <param name="selector">Selector, key selector</param>
        /// <param name="blockSize">Size of blocks (chunks/partitions)</param>
        /// <param name="values">Values as parameters</param>
        /// 
        /// <example>
        ///   /// <[!CDATA[
        /// /// The following EF query will hit a performance penalty or time out if EF gets a too large list of operationids:
        /// ///
        /// /// var patients = context.Patients.Where(p => operationsIds.Contains(p.OperationId)).Select(p => new {
        /// ///  p.OperationId,
        /// ///  p.
        /// /// });
        /// ///
        /// /// 
        /// /// var patients = context.Patients.AsExpandable().InRange(p => p.OperationId, 1000, operationIds)
        /// //.Select(p => new
        /// //{
        /// //    p.OperationId,
        /// //    p.IsDaytimeSurgery
        /// //}).ToList();
        /// //]]
        /// </example>
        /// <returns></returns>
        public static IEnumerable<T> InRange<T, TValue>(
                this IQueryable<T> source,
                Expression<Func<T, TValue>> selector,
                int blockSize,
                IEnumerable<TValue> values)
        {
            MethodInfo method = null;
          
            foreach (MethodInfo tmp in typeof(Enumerable).GetMethods(
                    BindingFlags.Public | BindingFlags.Static))
            {
                if (tmp.Name == "Contains" && tmp.IsGenericMethodDefinition
                        && tmp.GetParameters().Length == 2)
                {
                    method = tmp.MakeGenericMethod(typeof(TValue));
                    break;
                }
            }

            if (method == null) throw new InvalidOperationException(
                   "Unable to locate Contains");
            foreach (TValue[] block in values.GetBlocks(blockSize))
            {
                var row = Expression.Parameter(typeof(T), "row");
                var member = Expression.Invoke(selector, row);
                var keys = Expression.Constant(block, typeof(TValue[]));
                var predicate = Expression.Call(method, keys, member);
                var lambda = Expression.Lambda<Func<T, bool>>(
                      predicate, row);
                foreach (T record in source.Where(lambda))
                {
                    yield return record;
                }
            }

        }

        /// <summary>
        /// Similar to Chunk, it partitions the IEnumerable source and returns the chunks or blocks by given blocksize. The last block can have variable length
        /// between 0 to blocksize since the IEnumerable can have of course variable size not evenly divided by blocksize. 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="source"></param>
        /// <param name="blockSize"></param>
        /// <returns></returns>
        public static IEnumerable<T[]> GetBlocks<T>(
                this IEnumerable<T> source, int blockSize)
        {
            List<T> list = new List<T>(blockSize);
            foreach (T item in source)
            {
                list.Add(item);
                if (list.Count == blockSize)
                {
                    yield return list.ToArray();
                    list.Clear();
                }
            }
            if (list.Count > 0)
            {
                yield return list.ToArray();
            }
        }
        
  }

 
 
 

Linqkit allows us to rewrite queries for EF using expression trees. One class is ExpandableQuery. See the links here for further info about Linqkit and Linq-Expand.

 
  	/// <summary>Refer to http://www.albahari.com/nutshell/linqkit.html and
	/// http://tomasp.net/blog/linq-expand.aspx for more information.</summary>
	public static class Extensions
	{
		public static IQueryable<T> AsExpandable<T> (this IQueryable<T> query)
		{
			if (query is ExpandableQuery<T>) return (ExpandableQuery<T>)query;
			return new ExpandableQuery<T> (query);
		}
 

This all seems to look a bit cryptic, so lets see an integration test of mine instead:

 


        [Test]
        [Category(TestCategories.IntegrationTest)]
        public void GetDataChunkedDoesNotFail()
        {
            using (var context = DbContextManager.ScopedOpPlanDataContext)
            {
                int[] operationalUnitIds = new int[]{ 107455, 105431, 107646, 107846 };
                var reportItems = context.OperationalUnits.AsExpandable().InRange(ou => ou.FreshOrganizationalUnitId, 2, operationalUnitIds).ToList();
                Assert.IsNotNull(reportItems);           
                CollectionAssert.IsNotEmpty(reportItems); 
            }
        }

 
 
 

This shows how to use the InRange method of Marc Gravell. We use the AsExpandable method to allow us to hack into the expression tree of Entity Framework and the InRange method allows us to partition the work for EF. We do not know the siz of operational unit ids (usually it is low and another entity - operation Ids is of variable length and will in production blow up since we in some cases surpass the 2100 limit of Contains). And as I said before, Entity Framework will hit a performance bottleneck before 2100 parameteters are sent into the Contains method. This way of fixing it up will allow you to get stable running code in production again against large data and variable length. This code is tested with Entity Framework 6.2.0. Another article considers performance considerations for Contains and different approaches here: https://www.toptal.com/dot-net/entity-framework-performance-using-contains IMHO this approach has proven stable in a production environment for several years with large data and can be considered a stable workaround for EF slow Contains performance. I have made the LinqKit fork LinqKit.AsyncSupport available on Nuget here now: https://www.nuget.org/packages/ToreAurstadIt.LinqKit.AsyncSupport/1.1.0 This makes it possible to perform Async calls and expandable queries, i.e. queries with inline method calls for example. The nuget package now also sports symbol package for easier debugging experience. The source code for LinqKit.AsyncSupport is available here: https://github.com/toreaurstadboss/LinqKit.AsyncSupport