Tenure Derived Field

[jlemond]

I am trying to figure the number of years, rounded to the nearest hundredth that a person was with our company. I am using 3.10. I did a DATEDIFF(day,date1,date2) to get the number of days of tenure. I then divided that field by 365. My answer comes back as an integer, and I cannot seem to find a way to get it to stop rounding. Then, just to test, I tried to do a basic math function of 875/365. That came back as an integer as well. Any ideas or suggestions?

 

[BONZO123]

Try the following formula in the Derived Field formula. This might be what you need.


ROUND((( DATEDIFF(d, "PS_PERSONAL_DATA"."ORIG_HIRE_DT", ( GETDATE() )) )/365.25), 2)

 

[jlemond]

Thanks for the formula! I think I learned something about my problem when I tried it. I was using ReportSmith with ADP, but we moved to using Great Plains Payroll in-house now. Any reports that I point towards the Great Plains database report whole numbers only when doing a mathematical computation. It works fine when I point to ADP, but my data that I now need is in Great Plains. For example, I created a derived field of 875/365. When pointed at Great Plains, the field returns "2". When pointed at ADP, the field returns "2.3976". Any ideas as to why I seem to be stuck with integers now?

 

[CharlesCook]

Have you tried getting rid of the round

ROUND((( DATEDIFF(d, "PS_PERSONAL_DATA"."ORIG_HIRE_DT", ( GETDATE() )) )/365.25), 2)

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[mcbell]

Just ran into this the other day. Sounds like you are on SQL server. Here you go!

ROUND(CAST(DATEDIFF(MM,PS_PERSONAL_DATA.ORIG_HIRE_DT ,GETDATE() ) AS DECIMAL(5,2))/ 12,2)