Question expression to filter bindingsource

[muro]

Dear friends,

i have a column of type string which is holding values like 22.04.2018 17:30.
I want to use bindingsource.filter and keep only the records of Today.
i tested: dataTable1BindingSource.Filter = $"Datum like '{DateTime.Now.ToShortDateString()}'"; it works when i put some values in to the column without time but
unfourtently i have to keep the time in my columns too otherwise i would save onyl date. So how can i somehow trim the column values during the filtering so it has only 30.04.2018 instead 30.04.2018 17:30
Thanks.

 

[jmcilhinney]

First question: why are you using text fields to store date and time data? If at all possible, you should change that.

Assuming that you can't make that change, the whole point of the LIKE operator is that you can use wildcards to find partial matches. If you don't use wildcards (you are not) then LIKE is exactly the same as '=', which is obviously not what you want.

By the way, it's probably not safe to use ToShortDateString because that will use the default system format. If that code is executed on a machine with a different format to yours then it will fail, even if the data represents the same date. This is one reason why data and time data should be stored in native format and not as text: so text format is irrelevant. If you need a specific format then, unless you can 100% guarantee that every machine will have the same culture settings, you should call ToString and specify the format you want.

 

[muro]

Thank you sir, i will store it as DateTime then and i will remember your advise for custom string format.

 

[muro]

bestellungenBindingSource.Filter = $"Bestellzeitpunkt = #{DateTime.Now.ToString("dd/MM/yyyy")}#";

I am using this but i don´t get any results, there is no error but it is filtering all rows, also i have rows containing the date of today and older dates.
i was expecting everything woudl be filtered out except of 01.05:2018 11:33.

 

[jmcilhinney]

The issue is that you're filtering out anything that doesn't have a value of midnight on that day. You need to either trim the time from the column value or else specify a time range that covers the whole day. If this was SQL, I would go with the first option. This context only supports a very small subset of the SQL functionality though, so I'd go with the second option.

Actually, I just noticed that you're using the wrong date format too. Date literals, which you're creating in the filter string, MUST be in US format, i.e. month before day:

bestellungenBindingSource.Filter = $"Bestellzeitpunkt >= #{DateTime.Now:MM/dd/yyyy}# AND Bestellzeitpunkt < #{DateTime.Now.AddDays(1):MM/dd/yyyy}#";

 

[muro]

Works like a charm, thank you very much sir.