topic Re: Convert String to Date Issue in Getting Data In

Convert String to Date Issue

matthewfry57 — Tue, 27 Jul 2021 20:05:13 GMT

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:

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?

Re: Convert String to Date Issue

isoutamo — Tue, 27 Jul 2021 20:28:38 GMT

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

Re: Convert String to Date Issue

TheDeFoor — Tue, 27 Jul 2021 20:30:26 GMT

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?

Re: Convert String to Date Issue

matthewfry57 — Tue, 27 Jul 2021 20:34:07 GMT

Expiration_Date values look like this:

Re: Convert String to Date Issue

matthewfry57 — Tue, 27 Jul 2021 20:35:41 GMT

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

Re: Convert String to Date Issue

isoutamo — Tue, 27 Jul 2021 20:39:26 GMT

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

Re: Convert String to Date Issue

TheDeFoor — Tue, 27 Jul 2021 20:41:42 GMT

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

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

Re: Convert String to Date Issue

matthewfry57 — Tue, 27 Jul 2021 20:52:02 GMT

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

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

Re: Convert String to Date Issue

matthewfry57 — Tue, 27 Jul 2021 20:53:07 GMT

That was my problem!! Thank you much!!