Filtering on date in DataView RowFilter

[Raconteur]

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 doesn't 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

[Nerseus]

Try this:

"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 DataView's filter likes.

 

-Ner

[Raconteur]

Hi Ner,

 

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

 

"scheduled_completion_date < '" + System.DateTime.Today + "'"

 

works like a charm.

 

Much obliged!

[Nerseus]

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 machine's regional settings want. I don't 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, wouldn't work. You can test it by changing your regional settings - I might be wrong on this one.

 

-Nerseus

[RobVanMieghem]

Use the CultureInfo.InvariantCulture to format the date

[robertsams23]

you can try this.

 

http://vb.net-informations.com/dataview/filter-dataview.htm

 

robert.

[QuickDraw]

you can try this.

 

http://vb.net-informations.com/dataview/filter-dataview.htm

 

robert.

 

Thanks for this Robert, slightly late lol but its good information anyway, especially for me becuase i'm just starting to learn this stuff and every web page that can help me is a good one!

 

So thanks!