MSSQL actually stores DateTime in the millisecond epoch format(13 digits), not the second epoch format(10 digits).
You need to add the following section to the DBConnect v2 props.conf:
[source::YourSourceHere]
TIME_PREFIX = .{0,}DateModified=
TIME_FORMAT = %s%3N
TZ = ZA
The source needs to correspond to the source specified in the inputs.conf.
TIME_PREFIX:
In my case the date is in a column called DateModified which also happens to be the last column in my query(I'm doing a straight select of the date, no casting). I've found that if you don't force Splunk to include the entire import before your field it sometimes doesn't import the date correctly so the .{0,} in front of the column name is very important.
The TIME_FORMAT tells Splunk that its epoch format with 3 additional millisecond digits included and lastly the TZ indicated the time zone of the import.
If your DateTime column is not the last one in the select I would also add MAX_TIMESTAMP_LOOKAHEAD = 13 to force Splunk to only use the first 13 characters after the Regex is found.
... View more