
date problem  urgent please
can some one tell me the meaning of this suppose a =<BR>20000626 then what is the value of age<BR>Trim(Str(Fix(((IIf(DateDiff("d",DateSerial( Mid([a],1,4),Mid([a],5,2),Mid([a],7,2)),Now())<120,DateDiff("d",DateSerial(Mid ([a],1,4),Mid([a],5,2),Mid([a],7,2)),Now()),120))/30))))<BR>AS Age,<BR>

The complicated made simple...I hope
It's just an ugly bit of SQL (well, of an MS version of SQL, at least).<BR><BR>Let's take it apart:<BR><BR>Trim(Str(Fix(((IIf(DateDiff("d",Date Serial(Mid([a],1,4),Mid([a],5,2),Mid([a],7,2)),Now())<120,DateDiff("d",DateSerial(Mid ([a],1,4),Mid([a],5,2),Mid([a],7,2)),Now()),120))/30)))) AS Age<BR><BR>The DateSerial stuff, you will note, is identical in both places:<BR><BR>DateSerial(Mid([a],1,4),Mid([a],5,2),Mid([a],7,2))<BR><BR>And that's just converting 20000626 into a valid Date of June 26, 2000. That's needed because somebody put the date into the database in that silly string format instead of properly using a Date datatype for the field. Yuck.<BR><BR>Anyway, let's convert both uses of that into simply "theDate" to simplify the expression:<BR><BR>Trim(Str(Fix(((IIf(DateDiff("d" ,theDate,Now())<120,DateDiff("d",theDate,Now( )),120))/30))))<BR>AS Age<BR><BR>Now we notice that both of the "DateDiff" function calls are doing the same thing: They are finding how many *days* have elapsed between "theDate" and Now. So, again, let's simplify the expression by replacing those DateDiff calls with "numDays":<BR><BR>Trim(Str(Fix((( IIf(NumDays<120, NumDays ,120 ))/30)))) AS Age<BR><BR>Now we tackle "IIF". That's a special VB and VBA function (not available in VBScript) that means "if the first argument is true then return the second argument, otherwise return the third argument."<BR><BR>So <BR><BR>IIf(NumDays<120, NumDays ,120 )<BR><BR>means "if the number of days since the given date is less than 120, the value of this function is that number of days, but otherwise the value is 120". In other words, "give me the number of days since the given date, but never give me more than 120!"<BR><BR>Again, we'll simplify the expression and just call all of that "daysToUse", okay? So we get (eliminating some extraneous parentheses):<BR><BR>Trim( Str( Fix(daysToUse/30) ) ) AS Age<BR><BR>Now Fix just means convert "daysToUse divided by 30" to an integer, only. And since daysToUse *must* be somewhere between 0 and 120, then the result of Fix *must* be 0, 1, 2, 3, or 4. <BR><BR>And I would take that to be the number of "banker's months" between the original date and now. <BR><BR>The Trim and the Str are both completely unnecessary and kind of bogus: Str converts that 0 through 4 number to a string and then Trim lops off any spaces on either end of the string. But neither is needed. Fix is guaranteed to have returned a number from 0 to 4, and the representation of that single digit will always be a single character, so the author of this code was just being paranoid. Hmmm...unless that value is then used as a lookup key into some other table?<BR><BR>Finally the "AS AGE" means return the result of those calculations as the RecordSet name "Age". So in your ASP and ADO code, you can then do<BR><BR>Response.Write RS("Age") <BR><BR>and get a number from 0 to 4 written out.<BR><BR>As for what it means: <BR><BR>A guess, but probably pretty close on, would be that this is for an "aged receivables" listing, and the number indicates the "quality" of the receivable. Bills due less than 30 days are considered "current" (value 0). Bills 120 days or more overdue (value 4...remember the IIF function limited the number of days to 120, so 4 is max value) are considered "delinquent". Those in between (30, 60, and 90 daysvalues of AGE 1, 2, or 3) are still just considered "due".<BR><BR>Probably, the value of "Age" is used in deciding whether or not to send out late notices or "**THIS BILL IS SERIOUSLY OVERDUE**" notices or to send accounts to collection, or some such.<BR><BR>Whew. A lot of work for such a simple thing, no?<BR><BR>It could have been done *SO* much shorter if (a) the date was stored in the database as a Date datatype instead of the silly string and (b) the work was done in VBScript in ASP instead of in the middle of the SQL query.<BR><BR>But it should work, at least!<BR><BR>

Wow, I am in awe.
Bill, I hope to be as sharp in the ASP department as you... someday. And I am not being one bit facetious. I REALLY enjoyed reading this strip down/rebuild. You really kick *** at this stuff!

Yeah, but of whom...
<BLUSH><BR>Thank you, but consider that all I did was analyze code that somebody else had written! How about a bit of awe at their ability to create that? It's usually easier to be in my position, doing the analysis, then in his/hers, doing the creation of something like that. Yes?<BR></BLUSH><BR><BR>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

