none
Finding rows with specified month... RRS feed

  • Question

  • Hi.Here is my problem.

    I have a table Task with Date(type-Date) and Task(type-varchar(50)) columns in it.Now i want to show the rows from Task, where Date.Month is equal to my Month, which i pick up from calendar.

     

    how can i do that.

    Monday, September 26, 2011 3:42 PM

Answers

  • Ok, so here's a solution.  But I don't recommend it (refer to my previous post and just query the dang database to get your results instead of monkey'ing around with this hack). 

    Basically you would try to convert the Date field in the datatable to a string and run a LIKE statement with the integer value of the Month acquire from the DatePicker.  Also, "_CurDate" is the name of the Date field in the DataTable.

    'get the date and then the month values
    
    Dim d As Date = DatePicker.Value
    
    Dim month As Integer = d.Month
    
    
    
    'setup a string expression to run on the DataTable
    
    Dim exp As String = "Convert(_CurDate, 'System.String') LIKE '" & month & "%'"
    
    Dim relRows() As DataRow = YourDataTable.Select(exp)
    
    
    
    
    
    
    
    


    In the example above, if you selected today's date from the DatePicker, then the month variable would = 9 and any rows in your DataTable that are >=September 1st would be selected and added to the reRows() DataRow array.  This would include all years and strictly use 9 as the month to select.


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Edited by jamesfreddyc Wednesday, September 28, 2011 6:22 PM
    • Marked as answer by artxach Monday, October 3, 2011 7:28 PM
    Wednesday, September 28, 2011 6:21 PM
  • 	DataTable dt = new DataTable("Table");
    
    	dt.Columns.Add("Date"typeof(DateTime));
    	dt.Columns.Add("Task"typeof(String));
    
    	dt.Rows.Add("10/10/2010""Task1");
    	dt.Rows.Add("10/15/2010""Task1");
    	dt.Rows.Add("10/22/2010""Task1");
    	dt.Rows.Add("11/03/2010""Task1");
    
    	DataTable dtFiltered = (from a in dt.AsEnumerable()
           					where a.Field<DateTime>("Date").Month == 10
    					select a).CopyToDataTable();
    Just set the month that your calender control returns to the where clause in the query ..!
    With Best Regards, Boovendan M
    • Edited by BoovendanM Tuesday, September 27, 2011 4:55 AM
    • Marked as answer by artxach Monday, October 3, 2011 7:28 PM
    Tuesday, September 27, 2011 4:54 AM

All replies

  • 	DataTable dt = new DataTable("Table");
    
    	dt.Columns.Add("Date"typeof(DateTime));
    	dt.Columns.Add("Task"typeof(String));
    
    	dt.Rows.Add("10/10/2010""Task1");
    	dt.Rows.Add("10/15/2010""Task1");
    	dt.Rows.Add("10/22/2010""Task1");
    	dt.Rows.Add("11/03/2010""Task1");
    
    	DataTable dtFiltered = (from a in dt.AsEnumerable()
           					where a.Field<DateTime>("Date").Month == 10
    					select a).CopyToDataTable();
    Just set the month that your calender control returns to the where clause in the query ..!
    With Best Regards, Boovendan M
    • Edited by BoovendanM Tuesday, September 27, 2011 4:55 AM
    • Marked as answer by artxach Monday, October 3, 2011 7:28 PM
    Tuesday, September 27, 2011 4:54 AM
  • Thank You, but as i see you use LINQ, don't You? What  will the code be like, if i want only ADO.NET?
    Wednesday, September 28, 2011 3:58 PM
  • You really should do this at the database, especially if there are a large number of results to filter.

    If you are really, really, really set on attempting to run SQL on a DataTable (which is exactly what your problem is now), then you may have to implement a Month field (as type integer) in your DataTable and inlcude some automation to populate it with bits of what I mention below.

    Aside from that, the only help I can think of is that you will probably not be able to query a specific month from a Date field in a DataTable.  This is because the month portion would be identified as an integer value.  For example, below the msgbox would pop up a value of 4.

    Dim theDate As Date = '4/14/2011'

    Dim theMonth As Integer = theDate.Month

    MsgBox(TheMonth)

     

    In the end, just use SQL on the db-side to get the rows you need.


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Wednesday, September 28, 2011 5:48 PM
  • Ok, so here's a solution.  But I don't recommend it (refer to my previous post and just query the dang database to get your results instead of monkey'ing around with this hack). 

    Basically you would try to convert the Date field in the datatable to a string and run a LIKE statement with the integer value of the Month acquire from the DatePicker.  Also, "_CurDate" is the name of the Date field in the DataTable.

    'get the date and then the month values
    
    Dim d As Date = DatePicker.Value
    
    Dim month As Integer = d.Month
    
    
    
    'setup a string expression to run on the DataTable
    
    Dim exp As String = "Convert(_CurDate, 'System.String') LIKE '" & month & "%'"
    
    Dim relRows() As DataRow = YourDataTable.Select(exp)
    
    
    
    
    
    
    
    


    In the example above, if you selected today's date from the DatePicker, then the month variable would = 9 and any rows in your DataTable that are >=September 1st would be selected and added to the reRows() DataRow array.  This would include all years and strictly use 9 as the month to select.


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Edited by jamesfreddyc Wednesday, September 28, 2011 6:22 PM
    • Marked as answer by artxach Monday, October 3, 2011 7:28 PM
    Wednesday, September 28, 2011 6:21 PM