Filter not working for DateTime columns with "equal To" filt

Question

I can't get the filters for 'equal to' working on date time column in rad grid. As far as I can tell this has something to do with the times on the dates.

The date time column in database is saved as '2013-07-26 00:01:09.840' format. whereas in the grid the value is displayed as '7/26/2013 12:01:09 AM'. This should not be a problem as far as I think.
I am giving the value '7/26/2013 12:01:09 AM' in the text box to search with Filter option as 'Equal To'. No results are returned when I have selected this value from the grid itself.

Greater than and Less Than filter options work fine. Just the equal to is not.

Please tell me what is wrong here.

Bikesh Srivastava · Answer

http://docs.telerik.com/devtools/aspnet-ajax/controls/grid/how-to/Filtering/filter-for-griddatetimecolumn

Nitin Sontakke · Answer

Unfortunately, I will not be able to give code, because it is still not clear in which layer (C# or SQL Server stored procedure) you are doing the comparison. However, the concept is simple and it is as follows: 1/ User gives date in any format - dd/mm/yyyy mm/dd/yyyy but you must convert it to universally neutral date format yyyy-MM-dd. 2/ While doing comparison, always use BETWEEN (SQL Server) or > and 3/ For comparison purpose you add the time part manually. For example: User gives 03-Oct-2016 You compare as : '03-Oct-2016 BETWEEN '03-Oct-2016 00:00:00' and '03-Oct-2016 23:59:59'. Note that the time part we added is always hard coded and above expression returns true. Hope it helps. Any more question(s), please do not hesitate to ask.

ketan borsdiya · Answer

Hi, Provide date formate in tostring like , dt.ToString("dd/MM/yyyy")

Nageshwar Rao · Answer

hi nitin, For a "date" column if user want to filter it through "equals to" filter, then it requires the time part too, but my requirement is ”User can filter it only with date rather than using time”, so can you please help me here,i am using intersoft grid

Nitin Sontakke · Answer

Try to stay away from .ToString() method of DataTime structure. The method relies on machine's locale settings which could be different on different machines. You code working on your machine is NOT guaranteed to work in production environment. Alternative is of course to universally neutral date formats such as yyyy-MM-dd HH:mm:ss etc.while in C# part. One more thing, date is stored as an Integer in db. So statement like " The date time column in database is saved as '2013-07-26 00:01:09.840' format." is not quite correct. Is it only shown to you like that in whatever client you are using. Remember SQL Server Management Studio is also a client.

ketan borsdiya · Answer

HI, Where you compare, provide that code snippet..

Nageshwar Rao · Answer

DateTime dt = DateTime.MinValue; string test = FilterText.Trim(); if(DateTime.TryParse(test, out dt)) return "#" + dt.ToString() + "#";

ketan borsdiya · Answer

Hi Rao, Provide your code snippet..