How to calculate number of days to/from a date

Question

In a SearchKit query I'm looking to add a column that tells me the number of days from today to/from a date field. Basically datediff() from today.

• If the date is in the future, say tomorrow, the column would show 1 (positive).
• If the date is in the past, say yesterday, the column would show -1 (negative).

I tried the following options in Field Transformation...

• "Days to Anniversary": doesn't work if the date is more than a year away, and also doesn't work for comparing today to a date in the past (to give the negative number of days).
• "Days between two dates": there doesn't seem to be a way to set today's date as a comparison option;

I'm able to add a column "Current Date" from the Extras section, but I don't see a way to use it in the Field Transformation.

Appreciate any ideas.

Answer 1

You can use the field transformation and choose days between two dates.

Code:

[
  [
    "SavedSearch",
    "save",
    {
      "records": [
        {
          "name": "testdays",
          "label": "testdays",
          "form_values": null,
          "mapping_id": null,
          "search_custom_id": null,
          "api_entity": "Contact",
          "api_params": {
            "version": 4,
            "select": [
              "id",
              "DATEDIFF(CURDATE(), birth_date) AS DATEDIFF_birth_date_created_date",
              "birth_date"
            ],
            "orderBy": [],
            "where": [
              [
                "contact_type:name",
                "=",
                "Individual"
              ],
              [
                "birth_date",
                "IS NOT EMPTY"
              ]
            ],
            "groupBy": [],
            "join": [],
            "having": []
          },
          "expires_date": null,
          "description": null
        }
      ],
      "match": [
        "name"
      ]
    }
  ]
]

Answer 2

I think it would be a good to raise as an issue on https://lab.civicrm.org/dev/core/-/issues/new to discuss making the current date (and other Extra fields) available for use in Field Transformations.

For now, I think you could do this as a re-write using Smarty in a SearchDisplay with your date field and the current date field. See some details here.