Why does casting a SQL MAX(DateTime) command work in SSMS but not in C#?

Question

The following query works as expected in MSSSMS version 18.1 on an Azure hosted SQL database table:

select Name, cast(MAX(DTStamp) as Datetime) AS Last_Seen FROM dbo.MyTable GROUP BY Name

Example output:

Fairyflosser29   2021-11-11 19:26:00.323
GoofballAnticz   2021-11-25 14:43:57.443
WeirdAlJankyVic  2021-12-01 19:30:20.341

However, the same SQL command in C# run through the following methods always cuts the milliseconds from the DateTime field, despite the cast and despite the way I write the cast. This is critical for me as my follow-up queries depend on the accuracy of the DateTime field being correct to the millisecond, otherwise the subsequent queries return nothing.

The process calls GetLastSeenList() which returns a comma-delimited list of records that represent the most recent (by date time stamp) records for each Name in the table. With the populated list, another method is called later on that uses these results to pull the full row for each item.

Because the datetime stamps include milliseconds, those subsequent queries must include the millisecond values otherwise the query fails even though there is a record.

First, get the list of names and when they were last seen. Next, pull the full row for each record in the last-seen list (not included here as the first query is not providing date-times as they are in the table - with milliseconds intact).

I'm not very adept with SQL, so please help me understand what I'm missing here.

• Notes

  1: The DTStamp column is defined as datetime

  2: I've tried the cast inside and out, going to ridiculous lengths like: cast(MAX(cast(GPS_DTStamp as Datetime)) as Datetime) ...

[EDIT]: Just in case it's not clear, the sql command sqlcmd_GetLastSeenList is the same as the SSMS query at the top:

private static readonly string sqlcmd_GetLastSeenList =
  @"select Name, cast(MAX(DTStamp) as Datetime) AS Last_Seen FROM dbo.MyTable GROUP BY Name;";

GetLastSeen:

internal static async Task<string> GetLastSeenList()
{
    StringBuilder sb = new StringBuilder();
    var list = await ReadSQL(sqlcmd_GetLastSeenList, false);

    if(list != null && list.Count > 0)
    {
        // Just return a CSV list of ID's with last seen timestamps
        foreach(var record in list)
            sb.Append($"{record[0]} {record[1]},");
    }

    return sb.ToString().Trim(',');
}

ReadSQL:

private static async Task<List<object[]>> ReadSQL(string cmdText, bool isProd)
{
    List<object[]> response = new List<object[]>();
    string connect = string.Empty;

    if (isProd)
        connect = await SetConnectionProd(_uidProd, _p_pwd);
    else
        connect = await SetConnectionTest(_uidTest, _t_pwd);

    try
    {
        using (SqlConnection conn = new SqlConnection(connect))
        {
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                response = await ExecuteQuery2(cmd, conn);
            }
        }
    }
    catch (Exception ex)
    {
        //TODO: log it
        throw ex;
    }

    return response;
}

ExecuteQuery2:

private static async Task<List<object[]>> ExecuteQuery2(SqlCommand cmd, SqlConnection conn)
{
    List<object[]> retval = new List<object[]>();

    try
    {
        object[] myVals = new object[MAXCOLUMNCOUNT];
        object[] myRow = null;
        
        cmd.Connection.Open();
        using(SqlDataReader sqlreader = cmd.ExecuteReader())
        {
            if(sqlreader.HasRows)
            {
                while(sqlreader.Read())
                {
                    var count = sqlreader.GetValues(myVals);
                    myRow = new object[count];
                    Array.Copy(myVals, myRow, count);

                    retval.Add(myRow);
                }
            }
        }
    }
    catch(Exception ex)
    {
        throw ex;
    }
    finally
    {
        cmd.Connection.Close();
    }

    return retval;
}

Answer 1

The miliseconds are there. They're just not displayed by default by DateTime.ToString(). EG:

    var cmd = new SqlCommand("select cast('2021-11-11 19:26:00.323' as datetime) d", con);
    using (var rdr = cmd.ExecuteReader())
    {
        rdr.Read();

        var vals = new object[rdr.FieldCount];
        rdr.GetValues(vals);

        var val = (DateTime)vals[0];

        Console.WriteLine($"{val.GetType().Name}   {val}   {val.ToString("O")}");
    }

outputs

DateTime   11/11/2021 7:26:00 PM   2021-11-11T19:26:00.3230000

But also make sure you're not converting back and forth between datetime and datetime2, eg this

select cast(cast('2021-11-11 19:26:00.323' as datetime) as datetime2) d

outputs

d
---------------------------
2021-11-11 19:26:00.3233333

and this kind of conversion can be the source of mis-compares on subesquent queries.