We help IT Professionals succeed at work.

Setting a rowfilter on a dataset table

mgmhicks
mgmhicks asked
on
I have a xml file I load into a data set.  One of the fields ends up being a string date value of
"1900-01-01T00:00:00-05:00" {String}
    String: "1900-01-01T00:00:00-05:00"

This is the value it uses for a null value.  Anyway I need to create a rowfilter to see if the first 4 characters are "1900"

ds.tables(0).defaultview.rowfilter = "?????"

Normally I would go "Completedby = '" & ds.tables(0).rows(i).item(2).tostring" and I would be good to go, but what I need to do is something like "left(completedby,4)='1900', but when I do that it doesnt seem to get column information correct.  Any ideas, thanks


Comment
Watch Question

djon2003Senior software engineer
BRONZE EXPERT

Commented:
Ok, I though at first that your column isn't set a string but datetime.

So I tested your left function, which returns me 'Jan '. So the left function use the string representation of the date, which depends on your SQL settings.

What I suggest is to use DATEPART, as the code below.
DECLARE @t as table (filter datetime)

INSERT INTO @t
SELECT '1900-01-01'
UNION ALL SELECT '1960-01-01'
UNION ALL SELECT '1970-01-01'

select * from @t where DATEPART(yy, filter) = 1900

Open in new window

BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Try

ds.tables(0).defaultview.rowfilter = "yourcolumnname LIKE '1900%'"

Author

Commented:
this is the code, doesnt seem to be filtering.  Syntax issue?

dsGridMO.Tables(0).DefaultView.RowFilter = "completedby not like '1900%'"

Author

Commented:
Got it, thank you.  Using wrong field name, but your solution worked.

thanks
BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Glad to help :-)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.