none
how to filter date from date time column using datatable? RRS feed

  • Question

  • Hi there

    I have a datatable with onecolumn as datetime datatype. This table is filled with date and time values.

    say for example 11-02-2008 12:34 is stored in the datatable. Now i would like to filter as

    Dim datePartOnly as date = date.parse("11-02-2008")
    Dim DateRows() as datarows = datable.select("ColumnName ='" & datePartOnly & "'")

    This is not returning anything because datepartonly is of type date and i am searching in datetime column.

    I have tried using filter expressions such as

    1> datable.select("ColumnName LIKE '" & datePartOnly & "%'")     ---> This returning error saying that cant filter using with system.date and system.string data types

    2> Create an additional column at run time with only date as datatype and searched(filtered) using that column

    Is there any better way of doing this thing.....

    Please help!!

    Thanks

    • Moved by VMazurModerator Thursday, January 21, 2010 10:54 AM (From:ADO.NET Data Providers)
    Tuesday, January 19, 2010 9:34 AM

Answers

  • The following works for me (tested with Access database):

            Dim StartDate As Date = Date.Parse("11-02-2008")
            Dim EndDate As Date = StartDate.AddDays(1)
            Dim SQLFilter As String = "[DATE FIELD] >= #" & StartDate & "# AND [DATE FIELD] < #" & EndDate & "#"
            Dim DateRows() As DataRow = Datable.Select(SQLFilter)
    


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by prad9 Thursday, January 21, 2010 7:45 AM
    Tuesday, January 19, 2010 2:35 PM

All replies

  • The following works for me (tested with Access database):

            Dim StartDate As Date = Date.Parse("11-02-2008")
            Dim EndDate As Date = StartDate.AddDays(1)
            Dim SQLFilter As String = "[DATE FIELD] >= #" & StartDate & "# AND [DATE FIELD] < #" & EndDate & "#"
            Dim DateRows() As DataRow = Datable.Select(SQLFilter)
    


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by prad9 Thursday, January 21, 2010 7:45 AM
    Tuesday, January 19, 2010 2:35 PM
  • Thank u very much Paul it worked for me aswell..


    I thought there would be function like "Convert" to change column from datetime to date.

    But any way your solution looks simple.

    Thank You
    Thursday, January 21, 2010 7:48 AM