The expression contains undefined function call DATEADD() | FilterExpression | Datetime filter

Question

I have a grid-view and I want to filter it to display data only in a certain date interval (from date A to date B, include A and B)

I use SqlDataSource and FilterExpression, initially I use this FilterExpression:

FilterExpression="Submitted_time >= '{0}' AND Submitted_time <= '{1}'">

But data in {1} date is not included in the result (although I use '<=') (probably because of time (hour/minute) so I managed to add 1 day to the {1}:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CTECS %>" SelectCommand="SELECT * FROM [Event] ORDER BY [Id] DESC"
                        FilterExpression="Submitted_time >= '{0}' AND Submitted_time <= DATEADD(day, 1, '{1}')">
                        <FilterParameters>
                            <asp:ControlParameter Name="FromDate" ControlID="oCalendar1" PropertyName="SelectedDate" DefaultValue="1/1/1800" Type="DateTime" />
                            <asp:ControlParameter Name="ToDate" ControlID="oCalendar2" PropertyName="SelectedDate" DefaultValue="1/1/3000" Type="DateTime" />
                        </FilterParameters>
                    </asp:SqlDataSource>

But then Visual Studio give me : The expression contains undefined function call DATEADD()

Any solution here, my objective is only to include the data of the date {1} to the result...

Answer 1

Taken from: http://www.experts-exchange.com/Programming/Editors_IDEs/Q_22907598.html

[In reference to dateadd in filterexpression]

Nope, it's a string style filter only. You'll have to evaluate the date and then pass it in as a string to the filterexpression

E.g. you can't use built in sql functions in your filterexpression (at least that's how I'm interpreting it). You'll need to do a DateTime.AddDays(1) to your oCalendar2 date or ControlParameter toDate