Date Help 1

[pungigis]

I have a report that will be schedule to run on the 3rd of every month to cover anyone elibible on the 15th of the previous month as follows:

{enrollkeys.effdate} <= 15th of prev month
{enrollkeys.termdate} >= 15th of prev month
{enrollrider.effdate} <= 15th of prev month
{enrollrider.termdate} >= 15th of prev month
{benefit.effdate} <= 15th of prev month
{benefit.termdate} >= 15th of prev month

How do I tell it the 15th of the prev month in the above cases????

 

[PatriciaObreja]

ToText(Year(CurrentDate),0,"") & "/" & ToText(Month (CurrentDate) - 1, 0, "") & "/15"

Will return:
2008/1/15

The result must be converted back to DateTime before doing the comparations.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[pungigis]

How do I convert back to do comparisons??

 

[PatriciaObreja]

Use CDate() function.

CDate(ToText(Year(CurrentDate),0,"") & "/" & ToText(Month (CurrentDate) - 1, 0, "") & "/15")

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[smcnulty2000]

Of course you'd need to cover the December-January year changeover differently. Which requires a more complicated formula. You might try the dateadd function.

something like this:

dateadd("d",12, (dateadd("m",-1,{@date})) )

The inner version is supposed to subtract 1 month from the current date. Irrespective of the year or such issues. Then the outer version takes that result and adds 12 days to it. If you can't guarantee you'll run this on the 3rd, then it has to get more complicated.

Something like this would probably work.

local datevar dUseDate;
dUseDate:= {@date};
 
dUseDate:=
(dateadd("m",-1, dUseDate);

dUseDate:=
dateserial(year(dUseDate),month(dUseDate),15);

In this case, we create a local date variable, then load it with today's date last month, then we turn that value into the fifteenth of that month. Cdate works as well as dateserial on that part.

In each of the above situations you get a date back to work with, so your compare doesn't require another conversion.

Given the kind of report you are doing you may want to look into range data types. Its pretty good stuff if you want to know if a person was employed within a certain date range, or if you just need to know if some entry is inside of a range of entries. Not available in most languages, but Crystal has it.

 

[PatriciaObreja]

To cover the year changeover:
If Month(CurrentDate) = 1 then
CDate(ToText(Year(CurrentDate) - 1,0,"") & "/" & "12/15")
else
CDate(ToText(Year(CurrentDate),0,"") & "/" & ToText(Month (CurrentDate) - 1, 0, "") & "/15")

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[lbass]

I think all you really need to use is:

dateserial(year(currentdate),month(currentdate)-1, 15)

-LB

 

[smcnulty2000]

lbass, I think that has the same Dec-Jan problem.
Don't you agree?

 

[lbass]

No, dateserial accounts for that.

-LB

 

[smcnulty2000]

Just tested it. Nifty.

Adding it to my cookbook.