none
DataView with DateTime comparation RRS feed

  • Question

  • I have a DataTable which has a DateTime column .

     

    I want to view in my DataGrid only the rows that has any date and their time is between 12:00 and 16:00 how can i compare only the time???

     

     

    • Moved by VMazurModerator Thursday, April 15, 2010 10:08 AM (From:ADO.NET Data Providers)
    Thursday, March 30, 2006 9:07 PM

Answers

  • I got it!!!!!!!!!!!!!!

    I found this link

     

    DataColumn.Expression Property

     

    This is my final code:

    DataView dv = new DataView(ds.Tables[0]);

    dv.RowFilter = "CONVERT(SUBSTRING((CONVERT(MyDateTimeColumn,System.String)),12,19),System.DateTime) >= '08:00' AND " +

    "CONVERT(SUBSTRING((CONVERT(MyDateTimeColumn,System.String)),12,19),System.DateTime) <= '20:30'";

    Saturday, April 1, 2006 12:16 PM

All replies

  • What you do is create a DataView over the DataTable and set the DataView's RowFilter property to some expression that will filter down the results.  Then attach the DataView to the DataGrid.

    So DataView is like filter you can layer over the DataTable.

    If you have a DateTime you can use the Hour property to get the hours:

     

    DateTime dt1 = DateTime.Now;

    if (dt1.Hour > 12 && dt1.Hour < 16)

    {

    // Do something

    }

    Friday, March 31, 2006 5:06 AM
  • But how can I set my RowFilter property that will return me only the hourse between 12 and 16???
    Friday, March 31, 2006 8:07 AM
  • Ahhh OK i understand u

    ThanX :-)

    Friday, March 31, 2006 8:24 AM
  • Yeah something like:

    RowFilter = "[datefield].Hours >=12 and [datefield].Hours >=16"

    I'm a little fuzzy on the details of RowFilter syntax so don't quote me. (G)

    Friday, March 31, 2006 6:35 PM
  • It dosen't work...
    Friday, March 31, 2006 7:09 PM
  • Yes the expression syntax is somewhat limited.

    I'll see what I can find out and post back.

    Friday, March 31, 2006 9:10 PM
  • Ok, I had to do some hard thinking about this, so you owe me one.(G)

    RowFilter has limited set of operators, but they are designed this way so they can be fast an not require compiling a dynamic assembly (like regex does for example).

    So simple and fast.

    So to do what you want, I came up with this RowFilter (this is one big string):

     

    Convert(MyDate,'System.String') like '*12:*' or

    Convert(MyDate,'System.String') like '*13:*' or 

    Convert(MyDate,'System.String') like '*14:*' or 

    Convert(MyDate,'System.String') like '*15:*' or 

    Convert(MyDate,'System.String') like '*16:*'

    Thar you have it!

    Saturday, April 1, 2006 4:57 AM
  • You are my king man!!!!!!!!!

     

    This is just what i was searching for!!!

     

    by I have used this code instead:

    DataView dv = new DataView(ds.Tables[0]);

    dv.RowFilter = "Convert(ccc,'System.DateTime') >= '12:00' AND" +

    " Convert(ccc,'System.DateTime') <= '16:00'";

    dataGrid1.DataSource = dv;

     

    and it worked like a charm ...

    Saturday, April 1, 2006 10:58 AM
  • It workes only when the dates are equals....

    Is there a way to do this with >,< with converting to DateTime???

     

    is there a way to do somthing like this in TSQL but in my row filter???

    CONVERT(VARCHAR,myDateTime,103)

    Saturday, April 1, 2006 11:44 AM
  • I got it!!!!!!!!!!!!!!

    I found this link

     

    DataColumn.Expression Property

     

    This is my final code:

    DataView dv = new DataView(ds.Tables[0]);

    dv.RowFilter = "CONVERT(SUBSTRING((CONVERT(MyDateTimeColumn,System.String)),12,19),System.DateTime) >= '08:00' AND " +

    "CONVERT(SUBSTRING((CONVERT(MyDateTimeColumn,System.String)),12,19),System.DateTime) <= '20:30'";

    Saturday, April 1, 2006 12:16 PM
  • DataColumn.Expression Property

     

    If the link doesn't work this is the link

    ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.htm

    Saturday, April 1, 2006 12:20 PM
  • I was in a similar situation and your posts helped get me on the right path.

     

    I needed to just compare the month and year part of a dataview filter to a value.

     

    I used something like this:

     

    filtertext = " (Convert(DATE_ORDER_RECEIVED,System.String) like '" +

    filterDateRecMonthfromDDL + "/%" + "')";

    Since  my DATE_ORDER_RECEIVED field value was coming from a sql server 2005 datetime field the month is always cropped to 1 digit for jan thru september (no 01-jan, 02 - feb, but 1-jan, 2-feb) and since I exclude the '%' from the beginning of my value i am filtering on it will only go from the first part of the string which is the month (mm/dd/yyyy).

    Tuesday, September 19, 2006 12:40 AM
  • Hi there,

     

                  I have a similar problem but it is comparing dates only.... The error is invalid cast from Double to DateTime... I am using sql server 2005, this is my code

     

    dv.RowFilter = "Date_DT = CONVERT(" & dtmDateVar & " ,'System.DateTime')"

     

    please help me....

    Wednesday, June 6, 2007 2:36 AM
  •  

    Hi again,

     

    I found the answer this is very useful: try to look at this link below:

     

    http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=95799

    Wednesday, June 6, 2007 5:57 AM
  • I got it!!!!!!!!!!!!!!

    I found this link

     

    DataColumn.Expression Property

     

    This is my final code:

    DataView dv = new DataView(ds.Tables[0]);

    dv.RowFilter = "CONVERT(SUBSTRING((CONVERT(MyDateTimeColumn,System.String)),12,19),System.DateTime) >= '08:00' AND " +

    "CONVERT(SUBSTRING((CONVERT(MyDateTimeColumn,System.String)),12,19),System.DateTime) <= '20:30'";

    Nice little hack! Thanks for the trick!

    I had the opposite problem in that I only wanted to compare the date portion, not the time portion. The code for this was slightly different. For example, to show records for April 8, 2010, regardless of time, I was successful with this:

    CONVERT(SUBSTRING((CONVERT(MyDateTimeColumn,System.String)),1,10),System.DateTime) = #04/08/2010#


    It would be nice if the RowFilter syntax had a function to remove the time portion or date portion from a DateTime column instead of having to jump through hoops with the convert-substring-convert hack. Just my two cents.

    Tuesday, April 13, 2010 8:57 PM
  • Hi All,

    Thanks to all for useful tips...

    I have tried this tips in my case but I could not solve it.

    I have a DateTime column in my DataTable and want to filter dataview by Year only.

    Can anyone post a code for it.

     

    Thanks in advanced.

     

    Tejas

    Thursday, December 16, 2010 5:21 PM