How to obtain count of days from dates? 1

[peteschulte]

I need to obtain the count of days in the billing cycle. Below is one way, to use Olaf's post from before.

* billingsummary.cycle_end and cycle_star look like 03/21/09
* type is DateTime

How can I convert cycle_end and cycle_star to work with function dayofyear()?

Refer to thread 184-1016445 with the following contents.

* test
? dayofyear(date(2004,1,1))
? dayofyear(date(2005,3,1))
? dayofyear(date())

function dayofyear()
   lparameters tdDate
   return m.tdDate-date(year(m.tdDate),1,1)+1
endfunc

This seems to be a great utility. I'm curious what the 'm.' is.

More than that, I need to do something to cycle_star so that I can call dayofyear(somethingFromCycle_star).

I've tried many kinds of conversions and obtained many kinds of errors, learning a lot on background.

Thank you,
Peter

Pete S

 

[bborissov]

To use it with Datetime you need to convert the parameters first to date:

* test
? dayofyear(datetime(2004,1,1))
? dayofyear(datetime(2005,3,1))
? dayofyear(datetime())

function dayofyear()
   lparameters tdDate
   IF VARTYPE(tdDate) == [T]
      tdDate = TTOD(tdDate)
   ENDIF
return m.tdDate-date(year(m.tdDate),1,1)+1

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[brigmar]

Now that Borislav has addresses the main issue...

The "m." portion is the preferred way to reference variables.

If you omit the "m." portion, VFP will look at the currently selected table.


CREATE CURSOR temp ( thing C(5) )
INSERT INTO temp VALUES "WRONG"

thing = "RIGHT"

? thing && "WRONG"
? m.thing && "RIGHT"

 

[JRB-Bldr]

The DayOfYear() function is a good one.

But if what you are after is the number of days in the billing cycle, then....

Since VFP will easily provide you with the number days between Date1 and Date2 by merely subtracting the dates themselves, doesn't that meet your needs?

* -- Create Memory Variables --
* ---- At same time convert DateTime to Date Only Value ---
dCycle_End = TTOD(billingsummary.cycle_end)  
dCycle_Start = TTOD(billingsummary.cycle_star)

* --- Get Number of Billing Cycle Days ---
nBillingDays = (dCycle_End - dCycle_Start) + 1

(Note - 'd' and 'n' prefixes on the memory variables work to differentiate the mamory variable name from the associated table field name and, at the same time, visually assist in knowing the value type)

Good Luck,
JRB-Bldr

 

[peteschulte]

Thanks you jrbbldr for the solution I used, brigmar for the good answer and bborisov for the version I filed away for future use! This is what I was missing

IF VARTYPE(tdDate) == [T]
      tdDate = TTOD(tdDate)
   ENDIF

Well "days" happily is being stored to the table, after I switched from

replace mrworig.days with str(nDays)

to

replace mrworig.days with transform(nDays)

Til next time,

Pete S

 

[Olaf Doschke]

Support of jrbbldr from me, substracting two dates is the easiest to determine days. lastdate-firstdate gives you the difference in days betwenn two dates, +1 also counts the first date into the sum. If you do that with datetime values you get a difference in seconds.

The opposite is valid too, you can simply determine the date in a week by adding 7: ? Date()+7, you can determine the time in an hour by ? Datetime()+60*60

If you store transform(nDays) to mrworig.days, then that days field is a string field. I wonder why you store numbers in a string field. Keeping velues in their natural type as long as possible allows you to calculate on them etc without any (re)conversions. You can print all field types, you can format and align numeric fields, I see no reason the convert, unless the days field is not by your design, but that only moves the question to the designer of that table.

Bye, Olaf

 

[Olaf Doschke]

Also notice, that the dayofyear() function is bad for determining diference of two dates in different years. And so it's really not meant for that kind of calculations, you can use the date values directly for that, no need to convert to any number, also not julian date number, you could also get by a sys function.

Bye, Olaf.