Between Date Not working correctly

[#1]

Sample code:

=Count(if(datHired >= AddYears(DateFrom,-2) AND datHired <= AddYears(DateTo,-2) ,lngEmployeeID))

The above works perfectly unless the datHired is equal to the "To" Date (AddYears(DateTo,-2) )

Example:

If the To: date is '1/31'2008' the EmployeeID is excluded from the COUNT. The date is formatted straight from SQL as a DATE datatype, so there is no time confusion.

This is driving me nuts! Any and all help is greatly appreciated.

Thanks in advance!

[pover]

Just to make sure, do a num(addyears(DateTo,-2)) to make sure you get a whole number.

On a side note, I never knew the addyears() function existed. I've always used addmonths() because addyears() doesn't appear in the list of date and time functions in the help file. I wonder what others functions exist out there that aren't documented....

Regards.,

[#3]

Thanks, Karl!

Ran the number conversion and a whole number is returned as expected. Going to keep cranking away at this until I find a solution.

Oh and the AddYears function is only available in QV10 . . I believe.

[pover · Accepted solution]

Bummer. It's not that easy then.

I would create a straight table with IngEmployeeID as the dimension and AddYear(DateFrom,-2),AddYear(DateTo,-2), dateHired and the original count as expressions and see if you find anything strange.

Good luck.

[jonathandienst]

Hi

I think that Num only changes the display of the number, and not the internal representation. So it looks like a whole number, but in fact it still has a fractional portion.

To get a true whole number, use one of the round, floor or ceil functions, depending on how you want to truncate the date value.

Jonathan

[jonathandienst]

FWIW AddYears is available in QV9 (at least in SR5), but never documented!

[#7]

Turns out the Between process was functioning correctly, but I was excluding the data from the dimension.

Thanks for all of your help guys! Now can someone help me get this egg off of my face?