SQLCLR custom aggregate with datetime parameter

Question

Hy,

I have post a question about CLR User-Defined Aggregates few month ago on this post.

This works great with int. But now I would like to do the same functions with a datetime parameter.

But I can't get work. Like this, the code won't work... The problem is on the read function who generate this error on sql server :

System.ArgumentOutOfRangeException: Les graduations doivent être comprises entre DateTime.MinValue.Ticks et DateTime.MaxValue.Ticks.
Nom du paramètre : ticks
System.ArgumentOutOfRangeException: 
   à System.DateTime..ctor(Int64 ticks)
   à sMaxDatetime.Read(BinaryReader reader)

So I tried to convert my sql Datetime into ticks, with this, but it'not working eather. I have a OverflowException on the conversion to datetime.

I have found this post, and it's appear that I can't map my datetime to the BinaryReader...

So I running out of ideas to do my aggregate...

Have you a idea to do this ?

Here is the actual code :

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics.Eventing.Reader;
using System.Globalization;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Collections;
using System.IO;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToOrder = true,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = true,
    MaxByteSize = -1)]
public struct sMaxDatetime : IBinarySerialize
{
    #region Helpers

    private struct MyData
    {
        public string Data { get; set; }
        public DateTime? Group { get; set; }

        public int CompareTo(MyData other)
        {
            if (Group == null)
                return other.Group == null ? 0 : -1;

            if (other.Group == null)
                return 1;

            return Group.Value.CompareTo(other.Group.Value);
        }

        public static bool operator < (MyData left, MyData right)
        {
            return left.CompareTo(right) == -1;
        }

        public static bool operator > (MyData left, MyData right)
        {
            return left.CompareTo(right) == 1;
        }
    }

    #endregion

    private MyData _maxItem;

    public void Init()
    {
        _maxItem = default(MyData);
    }

    public void Accumulate(SqlString data, SqlDateTime group)
    {
        if (!data.IsNull && !group.IsNull)
        {
            var current = new MyData
            {
                Data = data.Value,
                Group = group.Value,
            };

            if (current > _maxItem)
            {
                _maxItem = current;
            }
        }
    }

    public void Merge(sMaxDatetime other)
    {
        if (other._maxItem > _maxItem)
        {
            _maxItem = other._maxItem;
        }
    }

    public SqlString Terminate()
    {
        return _maxItem.Data;
    }

    public void Read(BinaryReader reader)
    {
        //if (reader.ReadBoolean())
        //{
            _maxItem.Data = reader.ReadString();
            _maxItem.Group = new DateTime(reader.ReadInt64());
        //}
        //else
        //{
        //    _maxItem = default(MyData);
        //}
    }

    public void Write(BinaryWriter writer)
    {
        if (_maxItem.Group.HasValue)
        {
            writer.Write(true);
            writer.Write(_maxItem.Group.Value.ToString());
            writer.Write(_maxItem.Data);
        }
        else
        {
            writer.Write(false);
        }
    }
}

PS : I have this related post with sql_variant unclosed who could do the tricks but I can't get to work eather.

On which line you get this exception? – Soner Gönül Oct 19 '15 at 17:26 — Soner Gönül, Oct 19 '15 at 17:26

Ben Thul · Accepted Answer · 2015-10-20T16:52:41.290

I think you're working too hard with a custom struct to hold the datetime and name. Here's what I came up with:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, Name = "sMaxDatetime", MaxByteSize = -1)] 
public struct SO33215409 : IBinarySerialize
{
    private SqlString _data;
    private SqlDateTime _latest;
    public void Init()
    {
        _data = SqlString.Null;
        _latest = SqlDateTime.MinValue;
    }

    public void Accumulate(SqlString data, SqlDateTime dt)
    {
        if (dt > _latest)
        {
            _data = data;
            _latest = dt;
        }
    }

    public void Merge (SO33215409 Group)
    {
        if (Group._latest > _latest)
        {
            _data = Group._data;
            _latest = Group._latest;
        }
    }

    public SqlString Terminate ()
    {
        return _data;
    }

    public void Write (BinaryWriter w)
    {
        w.Write(_data.IsNull);
        w.Write(_latest.IsNull);
        if (_data.IsNull == false)
        {
            w.Write(_data.Value);
        }
        if (_latest.IsNull == false)
        {
            w.Write(_latest.Value.Ticks);
        }
    }
    public void Read(BinaryReader r)
    {
        bool dataIsNull = r.ReadBoolean();
        bool latestIsNull = r.ReadBoolean();
        if (dataIsNull)
        {
            _data = SqlString.Null;
        }
        else
        {
            _data = r.ReadString();
        }
        if (latestIsNull)
        {
            _latest = SqlDateTime.Null;
        }
        else
        {
            DateTime d = new DateTime(r.ReadInt64());
            _latest = new SqlDateTime( d );
        }
    }
}

And the SQL to exercise it:

WITH cte AS (
    SELECT * FROM (VALUES
    ('Manager'    , 'emp 1'    ,   dateadd(year, -35, getdate())),    
    ('Manager'    , 'emp 2'    ,   dateadd(year, -42, getdate())),    
    ('Developer'  , 'emp 3'    ,   dateadd(year, -36, getdate())),    
    ('Developer'  , 'emp 4'    ,   dateadd(year, -45, getdate())),    
    ('Developer'  , 'emp 5'    ,   dateadd(year, -22, getdate())) 
    ) AS x([Type], [Name], [DOB])
)
SELECT [Type], dbo.[sMaxDatetime]([Name], [DOB])
FROM cte
GROUP BY [Type]

Thanks @Ben ! Your right you solution is easier. Still a little problem with the null value. Ex: put null at the place of 'emp 5' — Cédric, Oct 20 '15 at 13:23
Just a slight modification in the `Read()` and `Write()` method to handle nulls; edits above. — Ben Thul, Oct 20 '15 at 16:54
Incidentally, CLR wouldn't be my first choice for this problem. This is almost trivially solvable with a simple common table expression. If writing the CLR as a learning experience, so be it. But it is more resistant to change (as you found out going from a numeric type to a datetime). — Ben Thul, Oct 20 '15 at 17:11
Ok it's work great thanks. And I know it's solvable with table expression, but it's for facilitate the writing in autogenerated dynamic SQL — Cédric, Oct 21 '15 at 10:00
Even given that requirement, I _still_ wouldn't use CLR here. Wrap the CTE in a view or a table-valued function and use that in your auto-generated SQL. In my (admittedly brief) testing, the CLR function performed worse than the CTE solution and is more opaque. Worst of all worlds. — Ben Thul, Oct 21 '15 at 14:28

SQLCLR custom aggregate with datetime parameter

1 Answers1