How to use DATEADD in Dataview Rowfilter??

Question

User-1355475649 posted

Hello, everybody.

I`m trying to display data to Datagrid when choose combobox item.

I already bind DB table to datagrid, and then filter data by combobox selection.

So I try to use DataView and rowfilter, but I have a problem.

In SQL this works well SELECT ListID, ListTitle, ListLastModifedDate from ListDB Where ListLastModifedDate <= DATEADD(MM, -1, GETDATE())

But In DataView.Rowfilter I try to like this

private void mtcbSiteColSearchCondition_SelectedIndexChanged(object sender, EventArgs e)
        {
            DataView dvSiteCol = new DataView(dtSiteCol);

            if (mtcbSiteColSearchCondition.SelectedItem.ToString() == "all")
            {
                mgrdSiteCollections.DataSource = dtSiteCol;
            }
            else
            {
                DateTime lastModifiedDate = DateTime.Now.AddMonths(-1);
                dvSiteCol.RowFilter = string.Format("SiteColLastModifyDate <= {0}",lastModifiedDate.ToString("yyyy/MM/dd/hh/mm/ss"), mtcbSiteColSearchCondition.SelectedItem.ToString());
                mgrdSiteCollections.DataSource = dvSiteCol;
            }
        }

In my DB [ListLastModifedDate] value is saved in this = 2016-09-05 04:22:11.000

How can I change that? please help me...

Answer 1

User-1716253493 posted

Try thia

dvSiteCol.RowFilter = string.Format("SiteColLastModifyDate <= {0}",lastModifiedDate.ToString("yyyy/MM/dd HH:mm:ss"));

or this

dvSiteCol.RowFilter = string.Format("SiteColLastModifyDate <= #{0}#",lastModifiedDate.ToString("yyyy/MM/dd HH:mm:ss"));

Answer 2

User1724605321 posted

Hi SuperRyden,

Date values are enclosed within sharp characters # #. The date format is the same as is the result of DateTime.ToString() method for invariant or English culture. For example :

dataView.RowFilter = "Date = #12/31/2008#"          // date value (time is 00:00:00)
dataView.RowFilter = "Date = #2008-12-31#"          // also this format is supported
dataView.RowFilter = "Date = #12/31/2008 16:44:58#" // date and time value
dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.DateTimeFormat, "Date = #{0}#", new DateTime(2008, 12, 31, 16, 44, 58));

You could try to format "mtcbSiteColSearchCondition.SelectedItem" with "yyyy/MM/dd HH:mm:ss" date and time format .

Best Regards,

Nan Yu