We help IT Professionals succeed at work.

Filter with Functions

JeepGeekin
JeepGeekin asked
on
Medium Priority
311 Views
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
        GridView1.DataBind()

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?
Comment
Watch Question

Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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.
Commented:
Have a look at this

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp

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 & "'"

Roger

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Great idea! Thanks! Worked perfectly.

...also, thanks BriCrow, but I was asked to avoid altering the sql since multiple people use it.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.