[2005] CAST/CONVERT Function in DataView Rowfilter

Hello,

Can anyone help me with the syntax for this please ?

I have a Datagridview which is populated by data in a dataview.
I want to filter the dataview on the Date Column using the RowFilter option, so the dates in the column will only be those later than today.

I don't really know how to do this, and additionally as the Date in SQL Server is held as Char(10) and not a datetime, when I bring the data in I also need to Cast/Convert it first.

I don't know the syntax for this though, so can anyone please help me ?

My code is as follows, and it's the syntax for 'xxx' I'm looking for :

VB Code:

dv = New DataView(ds.Tables(0))
dv.RowFilter = xxx
dgvDays.DataSource = dv

Assuming that your dates are stored in the format "MM/dd/yyyy" then your exp0ression would be:

VB Code:

String.Format("Convert([Date], 'System.DateTime') > {0:M/dd/yyyy}", Date.Today)

Also, as you're using VB 2005 I'd not recommend creating a DataView and binding it to the grid. I'd stick with the recommended binding method and bind your DataTable to a BindingSource and bind that to the grid, then set the Filter property of the BindingSource.

Thanks jmcilhinney,

My Date format is dd/MM/yy so I've changed the code to :

VB Code:

String.Format("Convert(myDate, 'System.DateTime') > {dd/MM/yyyy}", Date.Today)

However, I now get an error saying : "Input String was not in correct format"
Do you know what this is referring to ?

Is it something to do with the following part of your code, as I don't know what this means :

"0:M"

Cheers.

PS - I'm not really sure why we are using Dataviews in this way, but I'll certainly look into your advice - thanks for that.

It is simply a bad idea to store data in columns of inappropriate types. SQL date literals must ALWAYS be in the M/dd/yyyy format so that change you made is simply illegal. You've also removed the format place holder so today's date won't be inserted at all. Your big problem though is that your field values are stored in an incompatible format so they simply cannot be converted into dates using the limited functionality available to you in the RowFilter property. Only strings in the M/dd/yyyy format can be converted so you're out of luck. Dates should be stored in date columns, plain and simple.