# Date\_Diff function not working as expeceted
**URL:** https://discuss.elastic.co/t/date-diff-function-not-working-as-expeceted/385653
**Category:** Kibana
**Created:** [March 27, 2026, 6:25am UTC](https://discuss.elastic.co/t/date-diff-function-not-working-as-expeceted/385653 "2026-03-27T06:25:20Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Whoami1980](https://discuss.elastic.co/u/Whoami1980)
#### Post date: [March 27, 2026, 6:25am UTC](https://discuss.elastic.co/t/date-diff-function-not-working-as-expeceted/385653/1 "2026-03-27T06:25:20Z")
Query works well without this
"EVAL dd\_ms = DATE\_DIFF("day", last\_data, latest\_record)"
last\_data | latest\_record
2026-03-05T04:58:55.453Z|2026-03-04T17:09:30.155Z|
```auto
POST /_query?format=txt
{
"query": """
FROM .ml-anomalies*
| WHERE missing_field_count > 50
| STATS
last_data = MAX(last_data_time),
latest_record = MAX(latest_record_timestamp),
search_count = MAX(search_count),
missing_count = MAX(missing_field_count),
search_time = Max(total_search_time_ms)
BY job_id
EVAL dd_ms = DATE_DIFF("day", last_data, latest_record)
| SORT missing_count DESC
| LIMIT 100
"""
}
```
---
### Author:  [@RainTown](https://discuss.elastic.co/u/RainTown)
#### Post date: [March 27, 2026, 9:16am UTC](https://discuss.elastic.co/t/date-diff-function-not-working-as-expeceted/385653/2 "2026-03-27T09:16:07Z")
What, if any, error do you get? Does the query work in Kibana DevTools?
As posted, you are missing a pipe `|` character in the EVAL line.
This is your 19th new thread in less than a month. In an earlier thread, when I politely suggested perhaps invest some time in Elastic training to help build some knowledge, you told me you did not want to be spoon fed, I find the thread count a little bit ironic in that light. IMO you would still get more value, and perhaps faster progress, by spending some time understanding the fundamentals.
---
### Author:  [@Whoami1980](https://discuss.elastic.co/u/Whoami1980)
#### Post date: [March 27, 2026, 10:01am UTC](https://discuss.elastic.co/t/date-diff-function-not-working-as-expeceted/385653/3 "2026-03-27T10:01:53Z")
@RainTown
Lets set the record straight.
If there is time to spare in this work of mine. I would rather spend with my family and not touch elastic at all.
If its not because of current economic context and IT retrenchment and to bring income to the family to feed mouths i would not have choose this job.
Elastic so call vendor came and left a mess. So now with my limited elastic knowledge i am trying my best to clean it up.
If my memory serves me right there is no KPI in this forum so u can choose to ignore if u see my post .
Happy weekend . thanks very much
---
### Author:  [@stephenb](https://discuss.elastic.co/u/stephenb)
#### Post date: [March 27, 2026, 11:27pm UTC](https://discuss.elastic.co/t/date-diff-function-not-working-as-expeceted/385653/4 "2026-03-27T23:27:48Z")
```auto
last_data | latest_record
2026-03-05T04:58:55.453Z|2026-03-04T17:09:30.155Z|
```
> [@Whoami1980](#):
>
> ` EVAL dd_ms = DATE_DIFF("day", last_data, latest_record)`
@Whoami1980
You have to cast to `date` before
```auto
POST /_query?format=txt
{
"query": """
FROM .ml-anomalies*
| WHERE missing_field_count > 50
| STATS
last_data = MAX(last_data_time),
latest_record = MAX(latest_record_timestamp),
search_count = MAX(search_count),
missing_count = MAX(missing_field_count),
search_time = Max(total_search_time_ms)
BY job_id
| EVAL dd_ms = DATE_DIFF("day", TO_DATETIME(last_data), TO_DATETIME(latest_record)) <<< THIS
| SORT missing_count DESC
| LIMIT 100
"""
}
```
Also I notice you have
```auto
| EVAL dd_ms = DATE_DIFF("day", TO_DATETIME(last_data), TO_DATETIME(latest_record))
```
But the var is `dd_ms` so perhaps this is more correct
```auto
| EVAL dd_ms = DATE_DIFF("ms", TO_DATETIME(last_data), TO_DATETIME(latest_record))
```
Protip to test when you get down to it you can do stuff like this
```auto
ROW last_data="2026-03-05T04:58:55.453Z", latest_record="2026-03-04T17:09:30.155Z"
| EVAL dd_ms = DATE_DIFF("ms", TO_DATETIME(last_data), TO_DATETIME(latest_record))
```
```auto
{
"last_data": "2026-03-05T04:58:55.453Z",
"latest_record": "2026-03-04T17:09:30.155Z",
"dd_ms": -42565298
}
```
---
### Author:  [@Whoami1980](https://discuss.elastic.co/u/Whoami1980)
#### Post date: [March 31, 2026, 6:09am UTC](https://discuss.elastic.co/t/date-diff-function-not-working-as-expeceted/385653/5 "2026-03-31T06:09:25Z")
That works. Noted on your explanation will take note for future reference.