date compare for AccessDataSource

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.

Answer 1

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>

Answer 2

User1867929564 posted

instead why don't you change your query using datediff

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

 

Answer 3

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.

Answer 4

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.