locked
date compare for AccessDataSource RRS feed

  • Question

  • User-584964728 posted

    hi guys pleas I need your help about the date compare.

    I have a gridview connected to an access database and thers is a field for the date (Expiry Date).

    now what I need is to compare these date field  with the system date and if its greater than the system date  by 1 month then the record appear on the gridview if its else then doesn't.

    Wednesday, June 1, 2011 10:16 AM

Answers

  • User3866881 posted

    Hello:)

    Let's suppose say you've binded your Access file to the GridView, and let's say this——

    <asp:GridView……>

         ……………………

         <asp:Template HeaderText="xxx">

                   <ItemTemplate>

                           <asp:Label Id="lbDataTime" runat="server" …… Visible='<%#(DateTime.Now - DateTime.Parse(Eval("ExpiredDate"))).Days==1%>'/>

                   </ItemTemplate>

         </asp:Template>

    </asp:GridView>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 2, 2011 10:26 PM
  • User1867929564 posted

    instead why don't you change your query using datediff

    SELECT *
    FROM Table1 where datediff("m",date(),ExpiryDate)>1;

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 3, 2011 5:37 AM

All replies

  • User3866881 posted

    Hello:)

    Let's suppose say you've binded your Access file to the GridView, and let's say this——

    <asp:GridView……>

         ……………………

         <asp:Template HeaderText="xxx">

                   <ItemTemplate>

                           <asp:Label Id="lbDataTime" runat="server" …… Visible='<%#(DateTime.Now - DateTime.Parse(Eval("ExpiredDate"))).Days==1%>'/>

                   </ItemTemplate>

         </asp:Template>

    </asp:GridView>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 2, 2011 10:26 PM
  • User1867929564 posted

    instead why don't you change your query using datediff

    SELECT *
    FROM Table1 where datediff("m",date(),ExpiryDate)>1;

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 3, 2011 5:37 AM
  • User-584964728 posted

    So many Hello to you Decker, and thanks for replying, but I didn't get it so much. Anyway I made another solution with the SelectCommand for the AccessDataSource

     

    <asp:AccessDataSource ID="AccessDataSource1" runat="server"           

    DataFile="~/DB/AccessDB.mdb"          

    SelectCommand="SELECT [ID], [FullName], [ExpiryDate], FROM [Info] WHERE ExpiryDate - 30  <= ?">         

    <SelectParameters>

    <asp:ControlParameter ControlID="TextBox1" Name="CrrDate" PropertyName="Text" />          

    </SelectParameters>

    </asp:AccessDataSource>

    where the TextBox1 is the current date.

    its working fine on the day and the month, but the problem is in the year, it doesn't take the effect of change on the year.

    What I want to say lets assume the current date is 1/1/2011 (MM/DD/YYYY)

    and the ExpiryDate is 1/31/2011

    then it will subtract from the ExpiryDate 30 days and it will be 1/1/2011 thus, the record will appear or even if it less than the current date.

    But, if the ExpiryDate is 31/1/2012, the record also will appear and this is the error, its shouldn't appear, bcz its higher than the current date

     

    My question is how I can solve these problem (for a year) and is my way right or wrong,and how I can Disposal the TextBox1 and write on the select command the current date(the SQL statement for the current date)

    and sooo thanks for your helping.

    Friday, June 3, 2011 7:08 AM
  • User-584964728 posted

    thanks to you Kumar its working with me like I want on these  

    WHERE DATEDIFF("d",DATE(),ExpiryDate)<=30;

     

    so much thanks for you and for Decker.

    Friday, June 3, 2011 7:21 AM