How to change string from csv file to date format for proper sorting in search?

[jwolach2]

I have data that was imported from a .csv file. One of the field in the .csv file is called "date". However, when Splunk imports that field the data is a string in the format 8/22/2014. I would like to sort the data by this "date" field but, Splunk does not sort it correctly because it sees the data as a string and sorts based on left to right.

How can I make it so that I can sort on the "date" data as if it was an actual date?

[aweitzman]

Somewhere in your search string, add the following clause:

convert timeformat="%m/%d/%Y" mktime(date) as numdate

That will create a new field numdate containing the epoch numerical representation of your date field. You can then sort on numdate.

Read more about convert here:

http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Convert

[somesoni2]

You can try this

your base search to get data from csv | eval date=strptime(date,"%m/%d/%Y") | sort 0 date

[jwolach2]

Got it, I think.

sourcetype=csv | eval date=strptime(date,"%m/%d/%Y") | eval reformatted_date=strftime(date,"%m/%d/%Y") | sort 0 date AND inv | table date reformatted_date inv customer item vendor description | where (date > strptime("05/31/2013","%m/%d/%Y") AND date < strptime("07/01/2013","%m/%d/%Y"))

Thanks

[jwolach2]

Great!!! Thank you very much! So here's what I now have as my search statement.

sourcetype=csv | eval date=strptime(date,"%m/%d/%Y") | eval reformatted_date=strftime(date,"%m/%d/%Y") | sort 0 date AND inv | table reformatted_date inv customer item vendor | where reformatted_date < "01/31/14"

Now, I would like to select a date range to be displayed. How can I do that with the epoch numerical data?