CODEWITHSUNDEEP
c#sql-serverdate
Leron
Leron

Reputation: 9866

Filter datetime column in MS SQL 2008 server with a parsed string value

I'm not sure how I can make the title of this question more specific.

I am working on an ASP.NET MVC3 application. There I use jQuery grid to show data from the database and have few filters. One of them should allow the user to search by date.

In my database the Date column looks like this :

Date Column

I pass the selected date to the back end where I parse the date string like this :

 else if (property.PropertyType == typeof(DateTime))
                        {
                            DateTime value;
                            if (DateTime.TryParse(rule.Data, out value))
                            {
                                selector = ExpressionEquals<T>(rule.Field, value);
                            }
                        }

Where the variable value looks like this : 03/06/2013 12:24:30.

The problem is that in my database the Date column has a different format at least from what I can see in the Management Studio, and along with that in fact I don't want to filter so specifically (The time is only for user information, but in fact I need only the date).

I need make the data from the jQuery grid usable for the kind of search I want. In fact the date string that comes from the grid looks exactly like the parsed value. In other words the rule.Data value is "03/06/2013 12:24:30".

How can use this data to filter data only by date but keeping the table column type of datetime?

Upvotes: 3

Views: 1309

Answers (3)

Junaid S
Junaid S

Reputation: 41

you can use convert function sql sever e.g

SELECT field1, field2, field3, datetimefield FROM table1 WHERE Convert(date, datetimefield) = Convert(date, @datetimeparamter)

Upvotes: 1

Utkarsh
Utkarsh

Reputation: 127

Hey Leron use date format like this "03/06/2013 00:00:00" and "03/06/2013 23:59:59" may be it will help

Upvotes: 1

Denis Palnitsky
Denis Palnitsky

Reputation: 18387

Filter by range between "03/06/2013 00:00:00.000" and "03/06/2013 23:59:59.999"

Upvotes: 3

Related Questions