Some String Manipulation

[QFanatic]

hello all,

I am reading a value that looks something like this....

1/31/2022 0:0:0 AM SAST

I need to deduct the date from that field, subtract it from today, and get the Days difference.

So what I'm currently doing:

1.  left(ATTR_VALUE,(index(ATTR_VALUE,' ')) - 1)   ...I look for the first space and read everything to the left of it. This returns '1/31/2022'. It is NOT in date format yet so I do the following:

2. date(date#(left(ATTR_VALUE,(index(ATTR_VALUE,' ')) - 1),'mm/dd/yyyy'),'YYYY/MM/DD')....this returns '2022/01/31' which is correct

3. Then I need to get the Days difference between Today() and the date in point (2)...

INTERVAL(TODAY() - date(date#(left(ATTR_VALUE,(index(ATTR_VALUE,' ')) - 1),'mm/dd/yyyy'),'YYYY/MM/DD'),'D') as DaysDiffernce - which returns 10 days (as at today)

That is a LOT of coding to get a result....is there a quicker way of achieving the same result?

Thank you

[hic · Accepted solution]

One alternative is the following:

1) Use Subfield() in the script to extract the date:
SubField(ATTR_VALUE,' ',1)

2) Interpret this string - also in the script:
Date#(SubField(ATTR_VALUE,' ',1),'M/D/YYYY') as MyDate

2) No need to format intermediate results. You get the right answer simply by using:
Today() - MyDate as Difference

[marcus_sommer]

It depends. If you find a valid format-pattern for your timestamp the following might be enough:

interval(today() - timestamp#(FIELD, 'Format'), 'D')

I'm not sure if SAST could be fetched with the format-pattern. If not you need any additional measure like your cutting-method or maybe a removing with replace() or keepchar() or purgechar(). The next point is could there be real time-parts - if yes you may need a floor() to remove it (depends if you just show 'D' with interval() or if you want to calculate further with the result).

In each case you could remove the date() within your logic. Another approach to keep the script or expression more readable would be to place the logic within a (parametrized) variable.

- Marcus

[QFanatic]

I'm honored - two Guru's answering my question.😀

Thank you so much!