Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Filter with Functions

Posted on 2006-03-20
Medium Priority
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
ID: 16238735
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

Sancler earned 2000 total points
ID: 16238820
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

ID: 16238978
Great idea! Thanks! Worked perfectly.

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

571 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