No columns listed when choosing a column as timestamp

[jackreeves]

I am in the process of creating a new input in DBConnect. After running my SQL query which returns correct column, I then attempt to select a column as a timestamp, which is "Date Reported". "Date Reported" is in this format, 2017-12-15 08:39:23.0.

However DB Connect fails to identify any columns and returns "no matches" under the column drop down.

SQL Query is:
use osmq
SELECT "Date Reported" AS Date_Reported FROM Tickets_IT

Please could anyone advise!

Thanks

[cmerriman]

what version of db connect do you have and what type of db? postgres? mysql? can you try to put the timestamp in epoch? something like this: round(("Date Reported" -DATE '1970-01-01' ) * 86400,0) as epoch_date_reported ?
http://docs.splunk.com/Documentation/DBX/3.1.1/DeployDBX/Createandmanagedatabaseinputs

[jackreeves]

DB Connect Version - 3.1.1
DB Type - MS-SQL Server Using MS Generic Driver

Where would the round(("Date Reported" -DATE '1970-01-01' ) * 86400,0) as epoch_date_reported fit into my query? Not experienced with SQL.

Thanks

[cmerriman]

it would be in the select statement.

SELECT "Date Reported" AS Date_Reported, round(("Date Reported"  -DATE '1970-01-01' ) * 86400,0) as epoch_date_reported FROM Tickets_IT

[jackreeves]

Thanks - just tried this & get following error message:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '1970-01-01'.

[cmerriman]

sorry, that was originally for oracle sql. try something more like DATEDIFF(SECOND,{d '1970-01-01'}, "Date Reported") there are slight differences with different sql database syntaxes, so googling can help you if this doesn't work, as i'm not the most familiar with ms-sql.

[jackreeves]

That query works but hasn't resolved the issue of the issue of being unable to select a column for the timestamp.

Thanks