none
datatable.select method to get 1 year data RRS feed

  • Question

  • Hi,

    I want to query existing datatable to select exact 1 year data.

    I'm not sure how to query datatable using select method.

    something like:

    datatable.select(date between currentdate and currentdate - 1 year)

    Thanks,

    -N

     

    Thursday, January 19, 2012 12:42 PM

Answers

  • Hi Nilesh,

    Welcome to MSDN forum!

    You can refer to the following code sample to filter the 1 year data:

    string

    expression = "Date > #" + DateTime.Now.Date.AddYears(-1).ToShortDateString() + "# And Date <= #" + DateTime.Now.Date.ToShortDateString() + "#";

    var rows = table.Select(expression);

     

    For more detailed information about the filter expression which is used in DataTable.Select() method can found here, http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx.

    Good day!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    Friday, January 20, 2012 2:24 AM
    Moderator
  • Hi,

    For the column name "Post Date/Time", please add [] in the expression, or .NET will treat the \ signal as the divide operation. 

    string

    expression = "[Post Date/Time] > #" + DateTime.Now.Date.AddYears(-1).ToShortDateString() + "# And [Post Date/Time] <= #" + DateTime.Now.Date.ToShortDateString() + "#" ;

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    Friday, January 20, 2012 2:14 PM
    Moderator
  • Hi,

    I created some testing data based on your testing data.   First I added the "Post Date/Time" column as DateTime type:

                DataTable table = new DataTable();
                table.Columns.Add("ID", typeof(int));
                table.Columns.Add("Post Date/Time", typeof(DateTime));

                table.Rows.Add(new object[] { 1, new DateTime(2008, 11, 7, 21, 58, 0) });
                table.Rows.Add(new object[] { 2, new DateTime(2008, 10, 10, 20, 22, 0) });
                table.Rows.Add(new object[] { 3, new DateTime(2011, 10, 5, 15, 28, 0) });
                table.Rows.Add(new object[] { 3, new DateTime(2012, 1, 19, 19, 36, 0) });
                string expression = "[Post Date/Time] > #" + DateTime.Now.Date.AddYears(-1).ToShortDateString() + "# And [Post Date/Time] <= #" + DateTime.Now.Date.ToShortDateString() + "#";
                var rows = table.Select(expression);

     

    Also, I tried to declare the "Post Date/Time" column as type string, then I used the following codes to filter the values:

                DataTable table = new DataTable();
                table.Columns.Add("ID", typeof(int));
                table.Columns.Add("Post Date/Time", typeof(string));

                table.Rows.Add(new object[] { 1, "11/7/2008 21:58" });
                table.Rows.Add(new object[] { 2, "10/10/2008 20:22" });
                table.Rows.Add(new object[] { 3, "10/5/2011 15:28" });
                table.Rows.Add(new object[] { 4, "1/19/2012 19:36" });
                string expression = "CONVERT([Post Date/Time], System.DateTime) > #" + DateTime.Now.Date.AddYears(-1).ToShortDateString() + "# And CONVERT([Post Date/Time], System.DateTime) <= #" + DateTime.Now.Date.ToShortDateString() + "#";
                var rows = table.Select(expression);

     

    Good day!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 23, 2012 7:02 AM
    Moderator

All replies

  • Hi Nilesh,

    Welcome to MSDN forum!

    You can refer to the following code sample to filter the 1 year data:

    string

    expression = "Date > #" + DateTime.Now.Date.AddYears(-1).ToShortDateString() + "# And Date <= #" + DateTime.Now.Date.ToShortDateString() + "#";

    var rows = table.Select(expression);

     

    For more detailed information about the filter expression which is used in DataTable.Select() method can found here, http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx.

    Good day!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    Friday, January 20, 2012 2:24 AM
    Moderator
  • I tried below code:

    Datatable dt;

    dt.Columns.Add(

    "Post Date/Time");

    dt.Columns["Post Date/Time"].DataType = System.Type.GetType("System.DateTime");

    string

    expression = "'Post Date/Time' > #" + DateTime.Now.Date.AddYears(-1) + "# And 'Post Date/Time' <= #" + DateTime.Now.Date.ToShortDateString().ToString() + "#";

    datarow[] rows = dt.select(expression);

    and it gives me below error message:

    Cannot perform '>' operation on System.String and System.DateTime.

    Friday, January 20, 2012 10:10 AM
  • Hi,

    For the column name "Post Date/Time", please add [] in the expression, or .NET will treat the \ signal as the divide operation. 

    string

    expression = "[Post Date/Time] > #" + DateTime.Now.Date.AddYears(-1).ToShortDateString() + "# And [Post Date/Time] <= #" + DateTime.Now.Date.ToShortDateString() + "#" ;

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    Friday, January 20, 2012 2:14 PM
    Moderator
  • Yes, i did that and it seems it is working for me.
    Friday, January 20, 2012 2:26 PM
  • This conditon is getting executed with no data.

    Is it because of:

    [Post Date/Time] is in System.DateTime format and we are comparing it with DateTime.Now.Date.AddYears(-1).ToShortDateString()

    Also How can i use Between operator instead of And?


    Friday, January 20, 2012 3:11 PM
  • Hi Nilesh,

    Do you mean the query does not get the data you want? 

    Could you please give us with more detailed information about your DataTable object?  e.g. what columns it contains and their corresponding data types.  If we have some sample data, that would be great! 

    For the "BETWEEN" operator, I don't think DataTable.Select supports it now. 

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Friday, January 20, 2012 4:00 PM
    Moderator
  • datatable contains string data.

    sample data is shown in screen print below:

    and it should pull only highlighted data.

    Friday, January 20, 2012 5:30 PM
  • Hi,

    I created some testing data based on your testing data.   First I added the "Post Date/Time" column as DateTime type:

                DataTable table = new DataTable();
                table.Columns.Add("ID", typeof(int));
                table.Columns.Add("Post Date/Time", typeof(DateTime));

                table.Rows.Add(new object[] { 1, new DateTime(2008, 11, 7, 21, 58, 0) });
                table.Rows.Add(new object[] { 2, new DateTime(2008, 10, 10, 20, 22, 0) });
                table.Rows.Add(new object[] { 3, new DateTime(2011, 10, 5, 15, 28, 0) });
                table.Rows.Add(new object[] { 3, new DateTime(2012, 1, 19, 19, 36, 0) });
                string expression = "[Post Date/Time] > #" + DateTime.Now.Date.AddYears(-1).ToShortDateString() + "# And [Post Date/Time] <= #" + DateTime.Now.Date.ToShortDateString() + "#";
                var rows = table.Select(expression);

     

    Also, I tried to declare the "Post Date/Time" column as type string, then I used the following codes to filter the values:

                DataTable table = new DataTable();
                table.Columns.Add("ID", typeof(int));
                table.Columns.Add("Post Date/Time", typeof(string));

                table.Rows.Add(new object[] { 1, "11/7/2008 21:58" });
                table.Rows.Add(new object[] { 2, "10/10/2008 20:22" });
                table.Rows.Add(new object[] { 3, "10/5/2011 15:28" });
                table.Rows.Add(new object[] { 4, "1/19/2012 19:36" });
                string expression = "CONVERT([Post Date/Time], System.DateTime) > #" + DateTime.Now.Date.AddYears(-1).ToShortDateString() + "# And CONVERT([Post Date/Time], System.DateTime) <= #" + DateTime.Now.Date.ToShortDateString() + "#";
                var rows = table.Select(expression);

     

    Good day!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 23, 2012 7:02 AM
    Moderator
  • Glad to hear it works for you!

    Have a nice day!


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 23, 2012 2:11 PM
    Moderator
  • Hi,

        I have a datatable with a column having datetime in string format. and following is format

    yyyyMMddHHmmss . I wanto to compare it with DateTime.Now. I am unable to cast my colum value as datetime and then compare.

    Will you please guide me on this.

    Thank you,

    Muhammad


    Muhammad

    Friday, February 8, 2013 1:30 PM