A Problem with Dates (months)

[CustomerResearch]

Hello. I need to make a script that will pull records from an external source at half month intervals with the 15th of each month being the mid month draw line. For instance:

1st-15th Jan
16th-31st Jan
1st - 15th Feb
15th - 28th Feb

etc.

This is a new variation on something we sort of allready do. You see I have scripts set up to go back 3 days or a week and pull all the records. This isn't a big deal because the month has no effect on the individual weeks (I just use a multiple of the number of seconds in a day and go from there). The problem is if I try to do that with a script that will go back and pull records from 6 months or a year ago, and pull like I mentioned above, I can end up overshooting or undershooting depending on the month span (such as over Feb) or the number of days I use in a month (like 30).

Any suggestions?

-------------------------------
Brian, Customer Research Inc

http://www.customerresearch.com

 

[knob]

How do you determine what date range to use? Are users inputing a date and you have to figure out what date range it falls into?

aspect@aspectscripting.com

http://www.aspectscripting.com

 

[CustomerResearch]

No, the date isn't user entered (though come to think of it if worse comes to worse I could set up a script that prompted to get that info). The report is run bi-montly

One date needs to go back five months (This will be either the 1st or the 15th, depending on the time the report is run).

The second date needs to go back five and a half months (This will be either the 15th or the 28th/30th/31st depending on the month)

The problem with getting these variables is that I can't just go back 30 days and call that a month, because some months don't have exactly 30 days.

-------------------------------
Brian, Customer Research Inc

http://www.customerresearch.com

 

[RLHarris]

Not sure if you have to enter every date as a seperate report request, so this may have WAY more than you need. It parses out the month and day and counts backwards 5 months. Then the value is converted to a long which counts upward back to the current date. Hope you can use part of it.

proc main
integer Month, Day         ; Integers to contain date and
integer Year, Hour         ; time converted from long
integer Min, Sec           ; date and time.
string MonStr              ; String to contain month.
String TWODIGMO, TWODIGDAY, THISMONTH, THISDAY, THISYEAR, DATESTRING
Integer StrtMon, StrtDay, StrtYr
Long ScriptDate, StartDate, Diff
ScriptDate = $LTIME
ltimeints ScriptDate Year Month Day Hour Min Sec
monthstr Month MonStr   ; Get the month in string format.
If Day >15
  Strtday = 15
  StrtMon = Month - 5
Else
  Strtday = 1
Endif  
If StrtMon < 1
  StrtMon = StrtMon + 12
  StrtYr = Year - 1
Else 
  StrtYr = Year
Endif
Usermsg &quot;today is %s.  5 mos ago was %d %d %d&quot; $Date StrtMon Strtday StrtYr  
intsltime StrtYr StrtMon StrtDay Hour Min Sec StartDate
Diff = (ScriptDate - StartDate)/ 86400
usermsg &quot;%d days between then and now!&quot; Diff
While StartDate < ScriptDate
  ltimeints StartDate Year Month Day Hour Min Sec
  Numtostr MONTH THISMONTH     ; CONVERT TO STRING VALUES.
  NumtoStr YEAR THISYEAR   ;  
  NumtoStr DAY THISDAY
  Strcpy TWODIGMO &quot;0&quot;
  StrCat TWODIGMO THISMONTH ; ADDS STRING VALUE FOR 2 DIGIT CONVERSION.
  Strright TWODIGMO TWODIGMO  2 ; COPIES LAST TWO DIGITS BACK TO STRING
  Strcpy TWODIGDAY &quot;0&quot;
  StrCat TWODIGDAY THISDAY ; ADDS STRING VALUE FOR 2 DIGIT CONVERSION.
  Strright TWODIGDAY TWODIGDAY  2 ; COPIES LAST TWO DIGITS BACK TO STRING
  Strcpy DATESTRING TWODIGMO
  Strcat DATESTRING &quot;/&quot;
  Strcat DATESTRING TWODIGDAY
  Strcat DATESTRING &quot;/&quot;
  Strcat DATESTRING THISYEAR
  UserMsg DATESTRING
  StartDate = StartDate + 86400
EndWhile
UserMsg DATESTRING
endproc

Robert Harris

http://www.communicationsadvantage.com

 

[knob]

Here are a couple functions I came up with that may work for you. I didn't get to test any of the boundary conditions, but got the expected (to me at least) results when I set my date to 6/15 (I assumed this script would only be run on the 1st and 15th).

func FiveMonthsAgo : string
integer iDay, iMonth, iYear, iHour, iMin, iSec
string sDate

ltimeints $LTIME iYear iMonth iDay iHour iMin iSec ;Convert timeval to integer components
if iMonth < 6 ;If Jan. through May
iMonth = iMonth + 7 ;Then calculate five months ago
iYear-- ;And decrement year by one
else
iMonth = iMonth - 5 ;Otherwise just move month value back by five
endif
strfmt sDate &quot;%02d%02d%d&quot; iMonth iDay iYear ;Create MMDDYYYY string
return sDate
endfunc

func FiveandaHalfMonthsAgo : string
integer iDay, iMonth, iYear, iHour, iMin, iSec
integer iWeekday, iDayYear, iLeapYear
string sDate
long TimeVal

ltimeints $LTIME iYear iMonth iDay iHour iMin iSec ;Convert timeval to integer components
if iMonth < 7 ;If Jan. through June
iMonth = iMonth + 6 ;Then calculate six months ago
iYear-- ;And decrement year by one
else
iMonth = iMonth - 6 ;Otherwise just move month value back by six
endif
if iDay == 15 ;If this is the 15th of the month
switch iMonth
case 1 ;Months with 31 days
case 3
case 5
case 7
case 8
case 10
case 12
iDay = 31
endcase
case 4 ;Months with 30 days
case 6
case 9
case 11
iDay = 30
endcase
case 2 ;February
intsltime iYear iMonth iDay iHour iMin iSec TimeVal ;Convert to a long time value
ltimemisc TimeVal iWeekday iDayYear iLeapYear;Get leap year value
if iLeapYear == 1 ;If leap year
iDay = 29 ;Set day to 29
else
iDay = 28 ;Else 28
endif
endcase
endswitch
else
iDay = 15
endif
strfmt sDate &quot;%02d%02d%d&quot; iMonth iDay iYear ;Create MMDDYYYY string
return sDate
endfunc



aspect@aspectscripting.com

http://www.aspectscripting.com