locked
Problem with selecting data between dates RRS feed

  • 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?

    Friday, April 30, 2010 12:30 PM

Answers

  • 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 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 30, 2010 2:27 PM

All replies

  • 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 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 30, 2010 2:27 PM
  • 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) >= ?)" 

    Friday, April 30, 2010 3:20 PM
  • 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. 

    Friday, April 30, 2010 3:33 PM
  • User1789523204 posted

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

    but always use the best to choose from.


    Saturday, May 1, 2010 5:48 AM