SImple Bindingsource.Filter problem

muro

Active member
Joined
Apr 30, 2018
Messages
26
Programming Experience
3-5
C#:
 myBindingSource.Filter = $" StartTime not like 'now' AND (OrderTime >= #{DateTime.Now:MM/dd/yyyy}# AND OrderTime < #{DateTime.Now.AddDays(1):MM/dd/yyyy}#)";

I had this code and it worked good but then i decided to change OrderTime coloumn type in sql to nvarchar , it used to be datetime2. Unfourtunately this bindingsource filter above wont work anymore so i tried somethign like this but still no succes:
C#:
 myBindingSource.Filter = $" StartTime not like 'now' AND (CONVERT(OrderTime,System.DateTime) >= #{DateTime.Now:MM/dd/yyyy}# AND (CONVERT(OrderTime,System.DateTime) < #{DateTime.Now.AddDays(1):MM/dd/yyyy}#)";
 
Last edited:
Also would you recommend the Coloumn to be of type datetime2 instead? What is faster? I can´t decide which is more efficient.Thanks
 
You should NEVER store data that is not text in a text column. ALWAYS use a data type appropriate to the data. If you're storing dates then use a data type intended for dates. You can use date, datetime or datetime2, depending on exactly what you need to store. You should read the documentation for those data types and decide which is the most appropriate.
 
I'd advise having date in one field and time in another. It's just my preference to separate them, although you don't have to do it like that.
I would tend to separate them if they're going to be used separately or combine them otherwise. Of course, you don't always know exactly how data will be used when you design a database. Fortunately, it's always possible to combine separate values or separate combined values if you need to, in either SQL or application code.
 
"You should NEVER store data that is not text in a text column. ALWAYS use a data type appropriate to the data. "

I ment the sql coloumn sir, not the bindingsource or somethign else. Is is better to make my sql coloumn of tiype datetime2 or nvarcvhar. Also how would i make the bindingsource. filter work again? Thanks you so much everyone.
 
Last edited:
I ment the sql coloumn sir
So did I. I was referring specifically to the database schema, but the same thing applies everywhere. If there exists a data type that is specific to the data you're working with then use that data type. Don't just use a text data type for everything - or anything - because it seems easier. There are times where you have no choice, e.g. I believe that SQLite has no date-specific data type, but when you have the choice, ALWAYS use the dedicated data type. If you use the correct data type at the database level then that will apply up through the layers and your your filter will work as it is supposed to.
 
Allright then i will change the coloumn type to datetime2 again thanks a lot.
 
Last edited by a moderator:
I would tend to separate them if they're going to be used separately or combine them otherwise. Of course, you don't always know exactly how data will be used when you design a database. Fortunately, it's always possible to combine separate values or separate combined values if you need to, in either SQL or application code.
Yes, It's so easy to concatenate when they are already separated and in their own individual fields. And if they are already split, that saves you the hassle of needing to split and substring in code-behind files each time when you may only want a date portion or a time portion when taking them from a data-type that holds them both.

I would second using data-types for their designed and intended purpose to hold values of their own type, doing the opposite will likely cause headache later on as well as security risks.
 
Perhaps, I'm missing something. Why would you need to "split and substring in code-behind files each time when you need may only want a date portion or a time portion when taking taking them from a data-type that holds them both"?

Assuming that the data is stored in a datetime or datetime2, when loaded into C#, it would be loaded as a DateTime struct. To get the date part, just access DateTime.Date, and to access just the time part, access the DateTime.TimeOfDay.
 
Assuming that the data is stored in a datetime or datetime2, when loaded into C#, it would be loaded as a DateTime struct. To get the date part, just access DateTime.Date, and to access just the time part, access the DateTime.TimeOfDay.
Indeed and, if you need a String for display, you can call ToLongDateString, ToShortDateString, ToLongTimeString, ToShortTimeString or else call ToString and pass an appropriate date and/or time format specifier.
 
Yeah that is what i was using earlier. The coloumn type was datetime2 and all worked good. I have changed it back to datetime2 from nvarchar. Thanks to everyone
 
Back
Top Bottom