You have a couple choices. 1) You can execute your complex SQLstatement from AX - but if you do, be sure to use "trustworthy computing" options available in AX 4.0 and AX 2009. (See Dev IV training manual, chapter 2, or find the white paper on "Writing Secure Code". You would use CodeAccessPermission subclass for direct SQL first (I do not remember the name off-hand) then use the Connection and statement objects to execute your SQL. 2) You can get # days between two dates by using the function date2num which returns days since 1/1/1900. You do that to both dates and then get the difference. But you cannot execute a function from within AX SQL, so you need to do a less efficient loop - select the rows based on other conditions and then write if check on the date difference. That is not so bad if you do not extract a huge number of rows that you require date differences for.
On 12/3/08, vamsi praneeth <[EMAIL PROTECTED]> wrote: > > > > Not sure about difference in days, but if you want the difference in years > there's a method in Global class Global::yeardiff() which takes dates as > parameters. Try your luck with it. > > How can I figure out the difference in # of days between two dates? > > Can it be done using a select statement as well? > > The following t-sql is what I am trying to accomplish but if you can > answer one or both of the above questions would help me get there as > well.. > > T-SQL > select avg(datediff( day, transdate, closed)) from custtrans ct (nolock) > where accountnum=' aa122'and closed > '1/1/1900' and dataareaid=' demo' > and amountmst>0 and transdate >= getdate()-90 > > select custTrans where > localCustTrans. AccountNum= ='ba368' > && localCustTrans. Closed != datenull() > && localCustTrans. AmountMST > 0 > && localCustTrans. TransDate >= systemdateget( )-90; > > [Non-text portions of this message have been removed] > > > [Non-text portions of this message have been removed]
