topic Re: Can you help me change the format of this date field? in Splunk Search

Can you help me change the format of this date field?

sheaross — Fri, 19 Oct 2018 17:37:18 GMT

I have a modify date field in my ingested data. The date format of this field is MMDDYY with no "/" or "-".

Is there a way to change the format of this field during search to where it can be MM/DD/YYYY?

Also, I want the search to be able to read this modify field to only search and display events from the previous day. The ingested data is a complete file with only new entries added to the ingested data, so I can't use the time picker for last 24 hours. It will retrieve the entire file.

Re: Can you help me change the format of this date field?

493669 — Fri, 19 Oct 2018 17:56:50 GMT

In search query you can change time format-

...|eval date=strftime(strptime(<yourfieldname>,"%m%d%y"),"%m/%d/%Y")

also if you use TIME_FORMAT stanza in props.conf to set your time field as _time then you can use time range to search data...so that _time will be your time field

Re: Can you help me change the format of this date field?

sheaross — Fri, 19 Oct 2018 20:54:19 GMT

That works, for the dates that MMDDYY and I did not mention that some of the date do not have a 0 in front of the month. For example: September is 9 instead of 09. This query will only for months with two digits.
Any other ideas?

Re: Can you help me change the format of this date field?

Vijeta — Fri, 19 Oct 2018 21:21:43 GMT

You can use this

|eval c=len(<yourdatefieldname>)|eval y="0".<yourfieldname>|eval x = if(c=5,y,<yourfieldname>)|eval date=strftime(strptime(x,"%m%d%y"),"%Y/%m/%d")

Re: Can you help me change the format of this date field?

493669 — Sat, 20 Oct 2018 06:06:44 GMT

you can append leading zero to month if it is not present.-

|eval length=len(<yourfieldname>)|eval mon="0".<yourfieldname> |eval <yourfieldname> = if(length=5,mon,<yourfieldname>)|eval date=strftime(strptime(<yourfieldname>,"%m%d%y"),"%m/%d/%Y")

Re: Can you help me change the format of this date field?

sheaross — Mon, 22 Oct 2018 14:31:40 GMT

This answer worked for the leading zero.

Thank you.