How can I change a field to a date field?

[maria2691]

Hello Everyone

I have a field Month which has values like April 2017, May 2018,...
I am calculating with these using a stats command and I would like to sort by the Months in descending order.
When I use the sort command the values are sorted by Alphabetical order.
Hence I used strftime to convert the field into a date field, however I do not get any result.
Please find the query I have used below and help me with the corrections!

    source=*
    | fillnull value=0 "Budget Overrun percentage" 
    | eval Month=SUBSTR('Month',4, 15) 
    | eval "Budget Overrun percentage"= SUBSTR('Budget Overrun percentage', 1,1) 
    | stats values("Budget Overrun percentage") by Month 
    | sort by Month

Thanks
Maria Arokiaraj

[micahkemp]

Can you include what Month looks like prior to your changing it with substr?

[maria2691]

Hello @elliotproebstel

This does not seem to be working 😞
Not getting any results when using these commands.

[493669]

when you try below what output you are receiving? d

source=* | fillnull value=0 "Budget Overrun percentage" 

[elliotproebstel]

By line 5, if Month contains values like "April 2017", "May 2018", etc., then the following should work to replace line 6:

| eval parsable_date="01 ".Month
| eval sort_date=strptime(parsable_date, "%d %B %Y")
| sort sort_date

And if you don't like seeing those extra fields there, you could remove them by adding this to the end:

| fields - sort_date parsable_date

Basically, I create parsable_date because I don't think strptime can create an epoch date string for a date that doesn't have a day specified.