Why does my search only give strptime output for one of three time values?

[ppanchal]

I have a field DATE_OF_BIRTH and the values are like
1962-09-30 00:00:00.0

1955-10-21 00:00:00.0

1988-10-31 00:00:00.0

I am firing the below query,

index=aimsprod source=accident splunk_server=alpputl018 *100172697* OR *100172679* | fieldformat dobtime=strptime(DATE_OF_BIRTH,"%Y-%m-%d %H:%M:%S")  |  table DATE_OF_BIRTH,dobtime

The dobtime field only gives output for the value 1988-10-31 00:00:00.0.

Why it does not give output for the other 2 above mentioned values?

[lquinn]

I believe it will be because epoch time is calculated as the number of seconds since 1st Jan 1970. The two dates that are not being converted occur before this time, so the value would be negative, which is not supported by Splunk. What are you trying to achieve by converting the timestamps to epoch?

Might be worth taking a look at this answer:
https://answers.splunk.com/answers/214742/can-splunk-strptime-work-with-the-date-before-1970-1.html

[ppanchal]

The issue that I am actually facing is when I export the data to a csv file, the date/time format is changed to something like 00:00:00...
Is there a way I an fix this?

[lquinn]

Does this happen when you open the CSV in excel, or was the CSV saved from excel? Sometimes it does some weird auto-formatting stuff on dates ...
Do you just want to reformat it as YYYY-MM-DD?
If so, you could try just using the replace command instead of fieldformat:

| replace "* 00:00:00.0" with "*" in DATE_OF_BIRTH