Time format

[hxa27]

Hi,

I am using DB connect to fetch a table from the database which the table was imported as an Excel to the database. But, the problem is the time is showing like 1396760400.000 and tried different commands like convert, replace and other ones but the date is not changing. Also, when I tried just to rename the header of the field, I could not either. So, I have no idea what the problem is.
Idea/ suggestion would be great

Thanks

[harshavrath]

the date format that you are getting is an EPOCH time you can convert it by

to__char(date_field, 'DD-MON-YYYY HH24:MI:SS')

ex:SELECT to__char(date_filed, 'dd-mm-yyyy hh24:mi:ss AM') FROM table_name

[linu1988]

you wont be able to get in normal sql query. you need to do it splunk query. What is the database you are using?

[hxa27]

Non of these worked not because they are wrong but I am not able to change anything in the particular column. Idea in how to fix this issue

[harshavrath]

try this even this works

fieldformat new_date=strftime(dat_field,"%d-%m-%Y %H: %M: %S")

[linu1988]

try of convert ctime(datetime_column) is working?