Problem with selecting data between dates

Question

User-2066833408 posted

I have a web page with 2 calendar controls (start date and end date).  I am trying to select data that falls between the two selected dates but the start date needs to have one day added to it and the end date needs to have one day subtracted from it.

The query I was trying (which fails on missing parameters is:

<asp:AccessDataSource ID="AvailDataSource" runat="server" 
                        DataFile="~/App_Data/db.mdb" 
                        
                        
                        SelectCommand="SELECT ID, DATEADD([Day], 1, startDate) AS startHol, DATEADD([Day], - 1, endDate) AS endHol FROM booking WHERE (startHol <= ?) AND (endHol >= ?)">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="EndCalendar" Name="startHol" 
                            PropertyName="SelectedDate" Type="DateTime" />
                        <asp:ControlParameter ControlID="StartCalendar" Name="endHol" 
                            PropertyName="SelectedDate" Type="DateTime" />
                    </SelectParameters>
                </asp:AccessDataSource>

 Any idea on where I am going wrong?

Answer 1

User1789523204 posted

in asp.net there is datetime.addday function use that function

 

Dim today As System.DateTime
Dim answer As System.DateTime

today = System.DateTime.Now
answer = today.AddDays(1)

 

For Adding One Day

If You want subtract then Use

Answer = Today.addDays(-1)

In Sql Use Between Operator 

 

Like

Where Date Between @StartDate And @EndDate 

Answer 2

User-1199946673 posted

SelectCommand="SELECT ID, DATEADD('d', 1, startDate) AS startHol, DATEADD('d', - 1, endDate) AS endHol FROM booking WHERE (DATEADD('d', 1, startDate) <= ?) AND (DATEADD('d', - 1, endDate) >= ?)" 

Answer 3

User-2066833408 posted

Thanks abdulwakeel,

 

I moved the adding of the parameters to my codebehind and altered the access select staements to be more simple and it all works perfectly now. 

Answer 4

User1789523204 posted

There are many way to choose from when you are solving the problem

but always use the best to choose from.