How to convert a number into a Date?

[mattheuslima]

Hello, Folks. 

I have a field that represents a date but in this format (YY/MM/DD).

For example:  on 07/23/20 the field value will be 200723. 

I need to transform this value into a date (DD/MM/YY). 

I tried to use:

| eval MyDateEpoch=strptime(MyDate,"%Y%m%d")

but doesn't work. 

Can you help me ?

[to4kawa · Accepted solution]

| makeresults
| eval MyDate="200723"
| rex field=MyDate mode=sed "s/(\d\d)(\d\d)(\d\d)/\3\/\2\/\1/"

If MyDate is text, rex is useful.

[mattheuslima]

It work!!

Thank you !!!!

[isoutamo]

Hi

you should try “%y%m%d” as format string. %y is 99 and %Y is 9999.

r. Ismo

[mattheuslima]

Hi, @isoutamo . Thanks for answer!

Unfortunately it doesn't work. 

I got this as an answer   "Error in 'eval' command: The expression is malformed. Expected AND."

[isoutamo]

Please try this:

index=_audit
| head 1
| eval MyDate="200723"
| eval MyDateEpoch=strptime(MyDate,"%y%m%d")
| eval NewDate=strftime(MyDateEpoch, "%d/%m/%y")
| table MyDate MyDateEpoch NewDate