How to find days between today's date and field value date?

[rkassabov]

I am trying to subtract a field value date (Step Due Date) from today's date (nowstring) to determine if the number of days between them is less than half the SLA value.

This is the relevant part of my query but it doesn't seem to be working:

 | eval nowstring=strftime(now(), "%Y-%m-%d")
 | eval "Coming Due"=case(("Step Due Date"-nowstring) < (SLA / 2), 1, "Step Date Completed" != "", 0)

Here is an example value from "Step Due Date": 2018-06-18 12:11:07.890000

[MuS · Accepted solution]

Hi rkassabov,

your dates are in various formats, and because of the " the case() statement uses literally "Step Due Date" as string.
Try this one instead, where Step Due Date becomes an epoch timestamp which then can be subtracted with now():

| makeresults 
| eval SLA="2", "Step Due Date"="2018-06-18 12:11:07.890000", step_date_epoch=strptime('Step Due Date', "%F %H:%M:%S.%6N"), nowstring=now() 
| eval "Coming Due"=case(('Step Due Date' - nowstring) < (SLA / 2), 1, "Step Date Completed" != "", 0)

Adapt the SPL to your needs.

Hope this helps ...

cheers, MuS

[rkassabov]

Perfect, thank you!