Search code examples
crystal-reports

Calculate age of record today minus table record date

I am new to crystal reports with no training just fiddling and learning by example and by doing. I am trying to add a field to a report that will specify how old each record is in days based on the record date compared to today's date.

A fair bit of google and trying various example formulas.

For example a formula named Age with the following in it: DateDiff ("d",{Asgnmnt.DateAssign}, CurrentDate)

Actual results is that the formula is invalid.

So my database table name is Asgnmnt.DateAssign Which for an example record returns a date like 2019-09-06 Print Date (Date) in Crystal returns 9/12/2019 I want a field that will return the value 6 To tell me that the given example record is 6 days old And will do that for every other record returned on the report.

Please explain any help like I am below novice thank you.

Solution

  • This is easier to do in SQL. In your SELECT statement you can put as you had:

    SELECT DATEDIFF(DAY, Asgnmnt.DateAssign, GETDATE()) as [DaysOld]

    Update this query in Crystal Reports and you will see the new field available to use.