Filtering on DateTime column with non-midnight time component doesn't return valid FilterString

[will-scc]

I have a DateTime column with the format dd/MM/yyyy HH:mm:

The values in the underlying DataSource are from a database so have non-midnight time componennts, e.g. 25/06/2020 12:22:44.

This is recognised as a DateTime column:

However, when you try to filter by a specific date (you can't filter by a specific hour) it returns no rows .

If I inspect the FilterString that's created, it is producing this:

"((Convert([DATE RAISED], 'System.String') LIKE '%25/06/2020 00:00:00%'))"

The reason this filter returns no rows is because, for example, 25/06/2020 12:22:44 is not like 25/06/2020 00:00:00 (due to the time component).

This same problem exists even if I force the column Format like this: dgv.Columns("DATE RAISED").DefaultCellStyle.Format = "dd/MM/yyyy HH:mm:ss". The FilterString is still setting any time component to 00:00:00. I assume this is because here we're only changing the displayed value and not the underlying value in the DataSource to which the filter is being applied.

If I truncate these dates at the database/query level, the filtering works. This is obviously not an ideal fix when I need to display the time component.

[davidegironi]

Hello @will-scc. DateTime converstion works if the format of the DateTime is the same of you o.s. culture
ref. line 722 MenuStrip.cs (sb.Append("(Convert([{0}], 'System.String') LIKE '%" + Convert.ToString((IsFilterDateAndTimeEnabled ? dt : dt.Date), CultureInfo.CurrentCulture) + "%')" + appx);)

I think the problem should be there.

Can you run the Sample project first and check if it works there for you?
Then you could try formatting the string with the data and time format of your culture.

[will-scc]

Hi @davidegironi, sorry for the delayed reply. I wanted to make sure I tested it on the machine that had the problem.

The sample project does work on the machine, so it's definitely an application-specific problem.

I've forced the formatting of the column to match the culture (en-GB):

However, this still isn't recognising the time component and, as a result, the filtering isn't working:

As you can see, it's only picking up the dates not the times. My current culture is en-GB and the format is dd/MM/yyyy HH:mm:ss: dgv.Columns("DATE RAISED").DefaultCellStyle.Format = "dd/MM/yyyy HH:mm:ss"

Even if I remove all formatting and just have it default, it seems to be coming out as dd/MM/yyyy HH:mm which is odd. My data is loaded via ODP.NET from an Oracle database. If I inspect the underlying datasource the values are containing time components (evidence by forcing the formatting to show seconds).

Any ideas what might be going on here?

[will-scc]

Small bit of additional info. I've setup a brand new project to test this and the issue persists.

The complete code of my test:

private void Form2_Load(object sender, EventArgs e)
{
    var r = new Random();

    var dt = new DataTable();
    dt.Columns.Add("DATETIME", typeof(DateTime));

    for (int i = 0; i <= 10; i++)
    {
        var obj = new object[] { DateTime.Today.AddDays(r.Next(0, 7)).AddHours(r.Next(0, 12)).AddSeconds(r.Next(0, 30)) };
        dt.Rows.Add(obj);
    }

    advancedDataGridView1.DataSource = new BindingSource(dt, null);
}

[davidegironi]

I can not reproduce your behaviour.
Start from the Sample project provided in code. Does the "datetime"column work there?
To me it works, I've overriden the culture using App.Config ()

[will-scc]

I think I've narrowed down the problem to something related to loading data from databases (maybe the DataAdapter?).

I've taken the working sample project and replaced the SetTestData() and AddTestData() methods to load data from my database with this query:

I tried this with both Oracle.ManagedDataAccess.Client and System.Data.SqlClient and the result is the same. One looking at an Oracle 11g database, the other an SQL Server database, so I don't think the issue is at the database level.

private void AddTestData()
{
    string conn = "<redacted>";

    //for SqlClient test this was changed to select getdate()
    string query = @"select sysdate-2 from dual
                         union
                     select sysdate-1 from dual
                         union
                     select sysdate from dual
                         union
                     select sysdate+1 from dual
                         union
                     select sysdate+2 from dual";

    using (OracleConnection cnx = new OracleConnection(conn))
    using (OracleCommand cmd = new OracleCommand(query, cnx))
    using (OracleDataAdapter da = new OracleDataAdapter(cmd))
    {
        //Tried thiis true and false just in case
        da.ReturnProviderSpecificTypes = false;
        da.Fill(_dataSet.Tables["TableTest"]);
    }
}

Which loads like this:

The filtering then doesn't work:

Again, same problem in that the time component is not recognized. Note the FilterString.

I've double checked that the DataType of that column is definitely System.DateTime:

I'm at a loss to think what the cause of this could be... Any ideas?

[davidegironi]

Instead of adding the full datatable, iterate the table adapter and fill a single row, then add the row to the dataset binded to the bindingdatasource of the grid.
You can also take a look of the method I'm using here in the DGUIGHF (https://github.com/davidegironi/dguighf/), like this one here https://github.com/davidegironi/dguighf/blob/master/DGUIGHFSample/FormPosts.cs
Another example on a working project here https://github.com/davidegironi/dentned
I'm using this strategy on a big project, never had problem with this, and it has obvious advantages to split the database structure to the view one. Hope this helps.

[will-scc]

Do you know why adding a complete DataTable would cause this behaviour? It's very weird that it only happens on DateTime columns in this specific situation, everything else works fine.

Having to change how to load data from my database into datatables by avoiding using DataAdapter.Fill is going to add a lot of unnecessary extra code, I'd rather try and fix the underlying bug...

[davidegironi]

I've never investigate on this. I prefer to uncouple the DataAdapter cause it's related to the DB you are using. You should try with other DB sources, or google for something like "DataAdapter.Fill datetime conversion". Also looking at your query, it performs an union between tables, at first i will try with just one table, than makes thing little more complex.