CODEWITHSUNDEEP
asp.netc#-4.0datatable
Varinder
Varinder

Reputation: 1910

Convert datatype of column while filtering records using DataView.RowFilter

I have a datatable with a column having Date. Dates are stored as

2018/11/14 @ 12:10 PM.

2018/11/15 @ 13:49 PM.

2018/11/16 @ 15:37 PM. and so on

Now I have to filter records on the basis of date only.

I am filtering records using below code but it is not returning correct data.

dv.RowFilter = "SUBSTRING(DateColumn,1,10)>='2018/11/01' AND SUBSTRING(DateColumn,1,10)<='201811/30'";

I tried to cast substring value as we do in Sql 

(cast(DateColumn as date))

but it throws error.

Can anyone help me how I can get date part from that column and change it's data type to date.

Upvotes: 2

Views: 2339

Answers (1)

Rob
Rob

Reputation: 45809

The syntax you can use for RowFilters is described on learn.microsoft.com.

In order to convert the value in a given column to a date you need to use the CONVERT function, rather than cast.

(cast(DateColumn as date))

Would become:

CONVERT(DateColumn, 'System.DateTime')

Unfortunately I'd be surprised if the format of your string is one that will readily convert to a DateTime (though trying surely won't hurt on the off chance that it does!), something like this may give you some success though:

CONVERT(SUBSTRING(DateColumn, 1, 10), 'System.DateTime')

To which you can then apply your filter and say:

dv.RowFilter = "CONVERT(SUBSTRING(DateColumn, 1, 10), 'System.DateTime') >= '2018/11/01'";

Upvotes: 3

Related Questions