Filter with Functions

Posted on 2006-03-20
Last Modified: 2012-05-05
I have a date column that I want to apply a filter to (using the dataview rowfilter). My problem is that I need to filter it by just the Year of that column, not the whole date.

Something like this is what I am currently working with:
        Dim dv As New DataView(ds.Tables(0))
        dv.RowFilter = "Year(payment_date) = " & text1.text.trim
        GridView1.DataSource = dv

This gives me the error:  The expression contains undefined function call Year().
Is there a way to do this without altering the dataset possibly using date functions?
Question by:JeepGeekin
    LVL 34

    Expert Comment

    by:Brian Crowe
    You could add an additional column to your query and then filter on that.

    "SELECT ..., Year(payment_date) as payment_year, ... FROM ..."

    dim dv as new dataview(ds.tables(0))
    dv.rowfilter = "payment_year = " + text1.text.trim

    you will probalby want to add datagridcolumns to your gridview so that the payment_year isn't included.
    LVL 34

    Accepted Solution

    Have a look at this

    The same rules apply to dataview filter expressions.  There is no Year() function, but you could experiment with something like this, which would catch what you wanted if the date was converted to a string in the format dd/MM/yyyy or MM/dd/yyyy

     dv.RowFilter = "Substring(Convert(payment_date, 'System.String'),7,4) = '"  & text1.text.trim & "'"


    Author Comment

    Great idea! Thanks! Worked perfectly.

    ...also, thanks BriCrow, but I was asked to avoid altering the sql since multiple people use it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
    Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now