dbconnect ms sql timestamp error

[sduchene_splunk]

Hello, Question + answers here :
We were using dbconnect 2 for a MS sql query. the column used for the timestamp was giving us an error BIGINT.
(you can see it by typing index=_internal sourcetype=dbx2 error )
The trick is to convert the MS SQL epoch time, wich is in millinsecond, to a linux epoch time, in second, so that splunk can understand the time.
How do we do this ? answer below. hope it's useful for you

[sduchene_splunk · Accepted solution]

the idea is to convert the epoch time. Good thing to know : epoch time on win!=epochtime on linux....
the sql command that did the trick is : dateadd
and also divide by 1000 to get the epoch time in second instead of ms

dateadd(second, mysqlfield/1000,'19700101')