Cannot perform 'Like' operation on System.DateTime and System.String

Help
1

I am trying to filter a dt. The column has datetime values like 2/13/2018 3:02:00 AM. How can I filter by date?

My code: dt.Select(" [“+datetimeColName+”] LIKE ‘%“+row(0).tostring+”%’")

row(0).tostring is 2/13/2018

2

Hi @sangasangasanga

Well I suppose you want to keep all rows of a datatable where you have the same date as the first row to do this, you don’t need to use the LIKE operation you can use “=” instead and the expresion will be something like this:

dt.Select("datetimeColName= '"+dt.Rows(0)(indexOfYourDatetimeCol).ToString+"'")

Regards,
Reda Marzouk

3

You could try LINQ like below:

filtered_data_table = (From row In dt.AsEnumerable
Where row(datetimeColName).ToString.Contains(“2/13/2018”)
Select row).CopyToDataTable

Cheers

4

It’s normally recommended to compare dates as datatime values, rather than strings, which allow you to use > and < also.

I’m more familiar with the LINQ/lambda expressions:
dt.AsEnumerable.Where(Function(r) If(IsDate(r(datetimeColName).ToString.Trim), Convert.ToDate(r(datetimeColName).ToString.Trim) >= Convert.ToDate(row(0).ToString), False) ).ToArray

Replace .ToArray to .CopyToDataTable if desired, but I normally process them as an array.

My example basically iterates the dt variable as an enumerable and takes all rows where it is >= to the date in row(0).ToString. It also checks the iterated row value in dt that it is a date before converting to a datetime, or it will throw an error.

Using LINQ/lambda is a little easier to understand “I think” because it allows you to convert and check values as you normally would in VB.

Regards.

2 Likes
5

@reda @J0ska @ClaytonM Thank you for all your replies. You are suggesting to use linq. Is it possible to have more filters as my original filter line is like this:

dt.Select(" [“+usernameColName+”] LIKE ‘“+username+”’ and [“+datetimeColName+”] Like ‘%“+row(0).tostring+”%’ and [“+loginColName+”] LIKE ‘“+loginYes+”’").Length > 0

6

Sure. As many as you need :slight_smile:

7

@J0ska how can I do so? I have never used linq before…

8

I suppose like this:

filtered_data_table = (From row In dt.AsEnumerable
Where row(usernameColName).ToString.Contains(username)
And row(datetimeColName).ToString.Contains(row(0).tostring)
And row(loginColName).ToString.Contains(loginYes)
Select row).CopyToDataTable

9

Hi @J0ska, I tried like you suggested however I am getting an error.

image
image1064×679 35.1 KB

10

Hi,
Try isolate which of row(…).tostring… conditions is wrong by eliminatin one-by-one from the expression. Then focus on that one condition.

Cheers