topic Re: How to convert Aug-16 which is in text to a date format such that I can sort? in Splunk Search

How to convert Aug-16 which is in text to a date format such that I can sort?

ravitejaj — Wed, 09 Nov 2016 01:13:54 GMT

For example I have the below data as text:

Aug-16
Sep-16
Oct-16
Nov-16
Feb-16

When I sort it with Month, I wish to see:

Feb-16
Aug-16
Sep-16
Oct-16
Nov-16

Currently When I sort, it shows as

Aug-16
Feb-16
Nov-16
Oct-16
Sep-16

Re: How to convert Aug-16 which is in text to a date format such that I can sort?

HiroshiSatoh — Tue, 29 Sep 2020 11:43:10 GMT

How about converting for sorting?

・・・|eval sort_field=strptime(text_date,"%b-%d")|sort sort_field|fields - sort_field

Re: How to convert Aug-16 which is in text to a date format such that I can sort?

ravitejaj — Tue, 29 Sep 2020 11:43:13 GMT

Hey Horishi,

This is not working.. First of all strptime is not returning any data.

eval text_date="Aug-16" | eval sort_field=strptime(text_date,"%b-%d")|sort sort_field|fields - sort_field | table sort_field

Re: How to convert Aug-16 which is in text to a date format such that I can sort?

HiroshiSatoh — Tue, 29 Sep 2020 11:43:19 GMT

I am deleting the sort_field .

|fields - sort_field | table sort_field
↓
| table sort_field

Re: How to convert Aug-16 which is in text to a date format such that I can sort?

ravitejaj — Wed, 09 Nov 2016 04:24:57 GMT

Still the Sort is not working..

Re: How to convert Aug-16 which is in text to a date format such that I can sort?

ravitejaj — Wed, 09 Nov 2016 04:30:18 GMT

eval a=strftime(strptime(Approval_Month,"%b-%d"), "%b-%y") | table a | sort a

Re: How to convert Aug-16 which is in text to a date format such that I can sort?

gokadroid — Wed, 09 Nov 2016 04:37:42 GMT

Try this I am assuming your date is coming in a field myField

yourQuery to return myField
| eval newTime="00:00:00 01-".myField
| eval monthYear=strptime(newTime,"%H:%M:%S %d-%b-%y") 
| fieldformat monthYear=strftime(monthYear, "%b-%y")
| table myField, monthYear 
| sort monthYear

Try to replace last line | sort monthYear with | sort myField and see the difference.

Re: How to convert Aug-16 which is in text to a date format such that I can sort?

HiroshiSatoh — Tue, 29 Sep 2020 11:43:32 GMT

I mistook the day and the year.

eval a=strftime(strptime(Approval_Month,"%b-%d"), "%b-%y") | table a | sort a
↓
eval wk_Approval_Month="00:00:00:01-"+Approval_Month|eval a=strftime(strptime(wk_Approval_Month,"%H:%M:%S:%d-%b-%y"),"%Y-%m") | sort a

※a=2016-08