Hi Experts,
I have a list of dates in the field called my_date like below:
45123
45127
45130
How can I convert this?
Thank you!
It depends on what this number is supposed to represent
I tried to convert it but i couldn't get the exact results. Are there any other ways to convert it @ITWhisperer ?
Again - what dates are these numbers supposed to be?
It’s supposed to be based on the data @PickleRick
Can you give an example of what 45123 is supposed to be as a date? I can make a guess but it might be wrong which would waste everyone's time.
Format of the date you want based on that number? @ITWhisperer
How about YYYY/mm/dd?
Like this @ITWhisperer
YYYY-mm-dd
OK what date in YYYY-mm-dd format would you expect 45123 to be shown as?
I’m not sure. Give me an example so that I can try that @ITWhisperer
If I give you a conversion, how will you know whether it is correct or not?
I tried this query but it's showing something like this. But when i checked with an excel for this number 45123 - it's showing as 07/16/23. @ITWhisperer
Finally, the key piece of information! You are expecting this to be an Excel date value.
| makeresults
| eval date=45123
| eval _time=(date-25567-2)*24*60*60
Excel uses dates based on the start of the 20th Century 1900-01-01, counting in days, whereas, Splunk uses unix-style times based on seconds since 1970-01-01, so, you need to subtract the number of days between these two baseline points, and multiply by the number of seconds in a day. Note that Excel may not be calculating the date correctly since it indexes the first day as 1 (instead of 0) and incorrectly assumes that 1900 was a leap year (which it wasn't), hence the extra -2 days in the calculation.
Having said that, you will have to decide whether the _time value returned is correct based on the source of your data i.e. it could be a couple of days out.
Ok Thanks @ITWhisperer