How to remove time value (null) from string value that includes date and time value

[jsnyderlmco]

Hello all, I am trying to remove the time portion of the string value of a field that resides in our indexed data. The expiration field contains a string value as shown below as a date and time.

Query:

sourcetype="db" unique_id="00-201" expiration="*"
| eval mytime=strftime(strptime(expiration, "%m/%d/%Y"),"%m/%d/%Y") 
| table unique_id expiration | dedup unique_id

Results:

unique_id        expiration
00-201               2022-08-12 00:00:00.0

Goal: Perform a query to display expiration field value as 06-04-2022 and sort from oldest to newest

[richgalloway · Accepted solution]

It's not clear if you want to remove the timestamp from the event or just sort events by a timestamp that's not _time. I'll assume the latter.

Try this:

sourcetype="db" unique_id="00-201" expiration="*"
| dedup unique_id
| eval sortTime= strptime(expiration, "%m/%d/%Y")
| sort + sortTime
| eval expTime = strftime(sortTime, "%m-%d-%Y")
| table unique_id expTime

[jsnyderlmco]

Thank you for the quick response. It worked perfectly. One of the challenges we were experiencing was the inability to sort the expiration date as a date field. I appreciate the assist.