Getting Data In

Convert String to Date Issue

matthewfry57
Explorer

I have gone through the forums looking for an answer to this, but nothing has worked.  I am trying to convert a string to a date.  I have data in an index that is extracted, with a field named Expiration_Date that contains a string that is actually a date/time, such as 5/22/2022 10:10:25 PM.

I found that this query works properly:

| makeresults
| eval x="08/04/16 9:40:41 PM"
| eval y=strptime(x, "%m/%d/%y %H:%M:%S")
| eval z=strftime(y, "%m/%d/%Y")
| table x y z

This query outputs the converted time properly in the z field.  However, when I try to use this with my data, as such

index = ssl_certs
| eval x=Expiration_Date
| eval y=strptime(x, "%m/%d/%y %H:%M:%S")
| eval z=strftime(y, "%m/%d/%Y")
| table Expiration_Date, x, y, z

The x field is equal to the Expiration_Date field, but y and z fields are empty.  Is there something special I am missing here with loading in the values of the Expiration_Date field into the eval statements?

0 Karma
1 Solution

TheDeFoor
Explorer

It looks like you are getting a 4 digit year, so it'd be more like this -

| makeresults
| eval x="8/04/2016 9:40:41 PM"
| eval y=strptime(x, "%m/%d/%Y %I:%M:%S %p")
| eval z=strftime(y, "%m/%d/%Y")
| table x y z

Lowercase y is for 2 digit years, capital Y is for 4 digit years

View solution in original post

TheDeFoor
Explorer

Hello!

What is the data you're getting for Expiration_Date? It looks like you are expecting MM/DD/YY HH:MM:SS or 07/27/21 01:29:33. Is this the format of the Expiration_Date field? Can you provide an example?

0 Karma

matthewfry57
Explorer

Expiration_Date values look like this:

matthewfry57_0-1627418039417.png

 

Tags (1)
0 Karma

isoutamo
SplunkTrust
SplunkTrust
Hi
Can you try to use %I and %p as you are using 12h not 24h time?
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables
r. Ismo
0 Karma

matthewfry57
Explorer

I had tried this also, same result.

index = ssl_certs
| eval x=Expiration_Date
| eval y=strptime(x, "%m/%d/%y %I:%M:%S %p")
| eval z=strftime(y, "%m/%d/%Y")
| table Expiration_Date, x, y, z

matthewfry57_0-1627418134736.png

 

0 Karma

TheDeFoor
Explorer

It looks like you are getting a 4 digit year, so it'd be more like this -

| makeresults
| eval x="8/04/2016 9:40:41 PM"
| eval y=strptime(x, "%m/%d/%Y %I:%M:%S %p")
| eval z=strftime(y, "%m/%d/%Y")
| table x y z

Lowercase y is for 2 digit years, capital Y is for 4 digit years

matthewfry57
Explorer

That was my problem!!  Thank you much!!

0 Karma

isoutamo
SplunkTrust
SplunkTrust
You are sure that Expiration_Date is not a multivalue field or it’s not containing any additional characters?
0 Karma

matthewfry57
Explorer

Yup I am sure, here is an example of the extracted field.  No extra spaces or anything.

matthewfry57_0-1627418994175.png

The source data comes from a file that looks like this (obfuscated hostnames):

matthewfry57_1-1627419094233.png

 

0 Karma
Get Updates on the Splunk Community!

Leveraging Automated Threat Analysis Across the Splunk Ecosystem

Enhance Security Operations with Automated Threat Analysis in the Splunk EcosystemAre you leveraging ...

Splunk Developers: Go Beyond the Dashboard with These .Conf25 Sessions

  Whether you’re building custom apps, diving into SPL2, or integrating AI and machine learning into your ...

Index This | How do you write 23 only using the number 2?

July 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...