1. raji Guest

## 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())&#060;120,DateDiff("d",DateSerial(Mid ([a],1,4),Mid([a],5,2),Mid([a],7,2)),Now()),120))/30))))<BR>AS Age,<BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## The complicated made simple...I hope

It&#039s just an ugly bit of SQL (well, of an MS version of SQL, at least).<BR><BR>Let&#039s 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())&#060;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&#039s just converting 20000626 into a valid Date of June 26, 2000. That&#039s 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&#039s convert both uses of that into simply "theDate" to simplify the expression:<BR><BR>Trim(Str(Fix(((IIf(DateDiff("d" ,theDate,Now())&#060;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&#039s simplify the expression by replacing those DateDiff calls with "numDays":<BR><BR>Trim(Str(Fix((( IIf(NumDays&#060;120, NumDays ,120 ))/30)))) AS Age<BR><BR>Now we tackle "IIF". That&#039s 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&#060;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&#039ll 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&#039s 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 days--values 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>

3. Senior Member
Join Date
Dec 1969
Posts
2,031

## 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/re-build. You really kick *** at this stuff!

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Yeah, but of whom...

&#060;BLUSH&#062;<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&#039s usually easier to be in my position, doing the analysis, then in his/hers, doing the creation of something like that. Yes?<BR>&#060;/BLUSH&#062;<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
•