Filtering on DateTime column with non-midnight time component doesn't return valid FilterString about advanceddatagridview HOT 8 CLOSED

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.

from advanceddatagridview.

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?

from advanceddatagridview.

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);
}

from advanceddatagridview.

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 ()

from advanceddatagridview.

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?

from advanceddatagridview.

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.

from advanceddatagridview.

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...

from advanceddatagridview.

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.

from advanceddatagridview.