Answers.Body: <p>You can use the field transformation and choose days between two dates.</p>
  <p><a href="https://i.sstatic.net/vnZdj.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/vnZdj.png" alt="enter image description here" /></a></p>
  <p>Code:</p>
  <pre><code>[
    [
      &quot;SavedSearch&quot;,
      &quot;save&quot;,
      {
        &quot;records&quot;: [
          {
            &quot;name&quot;: &quot;testdays&quot;,
            &quot;label&quot;: &quot;testdays&quot;,
            &quot;form_values&quot;: null,
            &quot;mapping_id&quot;: null,
            &quot;search_custom_id&quot;: null,
            &quot;api_entity&quot;: &quot;Contact&quot;,
            &quot;api_params&quot;: {
              &quot;version&quot;: 4,
              &quot;select&quot;: [
                &quot;id&quot;,
                &quot;DATEDIFF(CURDATE(), birth_date) AS DATEDIFF_birth_date_created_date&quot;,
                &quot;birth_date&quot;
              ],
              &quot;orderBy&quot;: [],
              &quot;where&quot;: [
                [
                  &quot;contact_type:name&quot;,
                  &quot;=&quot;,
                  &quot;Individual&quot;
                ],
                [
                  &quot;birth_date&quot;,
                  &quot;IS NOT EMPTY&quot;
                ]
              ],
              &quot;groupBy&quot;: [],
              &quot;join&quot;: [],
              &quot;having&quot;: []
            },
            &quot;expires_date&quot;: null,
            &quot;description&quot;: null
          }
        ],
        &quot;match&quot;: [
          &quot;name&quot;
        ]
      }
    ]
  ]
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-10-04T20:48:54.950Z
Answers.Text: Interesting. That would seem to do it, except that on my Civi 5.65.0 the "Current Date" column doesn't show up under "Field Transformations" the way it's available in your screenshot. The current date displays in the results, but "Current Date" not selectable as a field under Field Transformations.
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-10-04T21:16:03.980Z
Answers.Text: Nope, it doesn't but i have added it manually. Created a search with dummy fields for date diff, exported the search. While importing changed the params for  DATEDIFF()
Answers.UserDisplayName: Pradeep Nayak
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-10-04T21:26:17.790Z
Answers.Text: Yes, perfect! Thank you. It's a hack but it works. I manually edited the api_params field directly in the database since I'm on a dev env. Using the SK import tool is much safer.
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-10-04T20:30:25.303Z
Answers.LastActivityDate: 2023-10-04T20:30:25.303Z
Answers.UserDisplayName: Pradeep Nayak
Answers.Body: <p>I think it would be a good to raise as an issue on <a href="https://lab.civicrm.org/dev/core/-/issues/new" rel="nofollow noreferrer">https://lab.civicrm.org/dev/core/-/issues/new</a> to discuss making the current date (and other Extra fields) available for use in Field Transformations.</p>
  <p>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 <a href="https://lab.civicrm.org/dev/core/-/issues/new" rel="nofollow noreferrer">some details here</a>.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-10-04T21:30:30.313Z
Answers.Text: Thank you. I'd prefer to do the calculation on the backend, which I have manually done with the hack provided by Pradeep. I'll open an issue because this should be more user-friendly.
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-10-04T21:53:08.973Z
Answers.Text: https://lab.civicrm.org/dev/core/-/issues/4673
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-10-04T20:28:13.023Z
Answers.LastActivityDate: 2023-10-04T20:28:13.023Z
Answers.UserDisplayName: Lars SG
Body: <p>In a SearchKit query I'm looking to add a column that tells me the <strong>number of days from today to/from a date field</strong>. Basically datediff() from today.</p>
  <ul>
  <li>If the date is in the future, say tomorrow, the column would show 1 (positive).</li>
  <li>If the date is in the past, say yesterday, the column would show -1 (negative).</li>
  </ul>
  <p>I tried the following options in Field Transformation...</p>
  <ul>
  <li><em>&quot;Days to Anniversary&quot;</em>: 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).</li>
  <li><em>&quot;Days between two dates&quot;</em>: there doesn't seem to be a way to set today's date as a comparison option;</li>
  </ul>
  <p>I'm able to add a column &quot;Current Date&quot; from the Extras section, but I don't see a way to use it in the Field Transformation.</p>
  <p>Appreciate any ideas.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-04T20:46:29.373Z
Comments.Text: Clarification: I'm on CiviCRM 5.65.0
Comments.UserDisplayName: Justin657
Tags: <searchkit><dates>
Title: How to calculate number of days to/from a date
ThreadText: # How to calculate number of days to/from a date
  
  (Asked by: username_0 on 2023-10-04)
  
  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.
  
  **Tags:** searchkit, dates
  
  > **Comments:**
  > **username_0**: Clarification: I'm on CiviCRM 5.65.0  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2023-10-04. Score: 2 | Accepted answer
  
  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"
        ]
      }
    ]
  ]
  
  > **Comments:**
  > **username_0**: Interesting. That would seem to do it, except that on my Civi 5.65.0 the "Current Date" column doesn't show up under "Field Transformations" the way it's available in your screenshot. The current date displays in the results, but "Current Date" not selectable as a field under Field Transformations.  
  > **username_1**: Nope, it doesn't but i have added it manually. Created a search with dummy fields for date diff, exported the search. While importing changed the params for  DATEDIFF()  
  > **username_0**: Yes, perfect! Thank you. It's a hack but it works. I manually edited the api_params field directly in the database since I'm on a dev env. Using the SK import tool is much safer.  
  
  ---
  
  ### Answer by username_2 on 2023-10-04. Score: 1
  
  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](https://lab.civicrm.org/dev/core/-/issues/new).
  
  > **Comments:**
  > **username_0**: Thank you. I'd prefer to do the calculation on the backend, which I have manually done with the hack provided by Pradeep. I'll open an issue because this should be more user-friendly.  
  > **username_0**: https://lab.civicrm.org/dev/core/-/issues/4673  
  
  ---
  
  