Filtering on date in DataView RowFilter

R

Raconteur

Member
Joined
Oct 23, 2003
Messages
8
Location
San Francisco
Hi Gang,

I am trying to do something that must be simple but cannot for the life of me figure it out... first off, I am using VB.NET and SQL Server.

What I want to do is filter the records of the DataSet underlying my DataView by using the RowFilter property of the DV. The problem is the column I want to filter on is an SQL DateTime. So, basically what I am looking for is something like this:

"scheduled_completion_date < " + System.DateTime.Today

in the RowFilter property.

When I do this I get an error that a System.DateTime cannot be compared to a System.Double (the Today value). So I try to use FromOADate on it:

"scheduled_completion_date < " + System.DateTime.FromOADate(System.DateTime.Today)

but VS.NET complains at that call saying that if I pass DateTime.Today as the argument, I must first convert it to a Double by using ToOADate. So now my call looks like:

"scheduled_completion_date < " + System.DateTime.FromOADate(System.DateTime.Today.ToOADate)

which is just ridiculous... but it STILL doesnt work. I get the same "cannot compare DateTime to Double" error!

How in the world do I do this?!?!?

TIA for any input.

Cheers,

Chris
 
Try this:
Code: 
"scheduled_completion_date < " + System.DateTime.Now.ToString("MM/dd/yyyy") + ""

First, I surrounded the date value with double quotes. Then I used DateTime.Now instead of Today (not much difference that I know of). Last, I formatted the date with ToString to match what (I hope) the DataViews filter likes.

-Ner
 
Hi Ner,

Thanks a million. It turns out the problem was the single quote. Even going back to:

Code: 
"scheduled_completion_date < " + System.DateTime.Today + ""

works like a charm.

Much obliged!
 
Excellent!

As a note, you might want to consider using ToString with an explicit format as I think not using an explicit format will use whatever the machines regional settings want. I dont think the filtering in the DataSet is as nice with the regional settings. Meaning, if your regional settings is yyyymmdd (no slashes or dashes) then using "System.DateTime.Today" will produce a string in the yyyymmdd format. The filter, I would guess, wouldnt work. You can test it by changing your regional settings - I might be wrong on this one.

-Nerseus
 
Back
Top