Splunk Search

How to convert multiple date formats in same field?

mistydennis
Communicator

I have some data coming in with multiple date formats in the same field, and I'm having trouble reporting on these dates. I'd like to keep the dates consistent - how do I create a statement to change just the dates that are in the undesirable format?

What I have:

DateAdded

2021-11-03
2/15/2022
1/13/2023

 

What I would like:

DateAdded
2021-11-03
2022-02-15
2023-01-13
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

There are plenty of ways to do this using various forms of eval. You can use if/case to match the types then convert based on match type as suggested, or you could do something like

| eval date=coalesce(strptime(DateAdded, "%m/%d/%Y"), strptime(DateAdded, "%F"))

which would parse each date/time until one of them converts to non-null

 

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

There are plenty of ways to do this using various forms of eval. You can use if/case to match the types then convert based on match type as suggested, or you could do something like

| eval date=coalesce(strptime(DateAdded, "%m/%d/%Y"), strptime(DateAdded, "%F"))

which would parse each date/time until one of them converts to non-null

 

0 Karma

Tom_Lundie
Contributor

Here is a one liner:

| eval DateAdded = if(like(DateAdded, "%/%/%"), strftime(strptime(DateAdded, "%m/%d/%Y"),"%Y-%m-%d"), DateAdded)

Based off your examples, I have assumed that the dates will either be in YYYY-mm-dd format or dd/mm/YYYY format.
If there are more than two formats, you might want to break these out into multiple fields using a rex command.

0 Karma
Get Updates on the Splunk Community!

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Splunk App for Anomaly Detection End of Life Announcement

Q: What is happening to the Splunk App for Anomaly Detection?A: Splunk is officially announcing the ...

Aligning Observability Costs with Business Value: Practical Strategies

 Join us for an engaging Tech Talk on Aligning Observability Costs with Business Value: Practical ...