Splunk DB Connect - unable to specify to_date in the rising column

[abpe]

I have configured a database input with a query that finishes with the following code:

Where TimeStamp > '2013-06-20 15:00:00.000' {{ AND $rising_column$ > to_date(?,'YYYY-MM-DD"T"HH:MI:SS.MS')}}

Unfortunately I am getting the following error in dbx.log:

2013-06-20 16:37:44.302 dbx1599:ERROR:TailDatabaseMonitor - Error while executing database monitor: java.sql.SQLException: 'to_date' is not a recognized built-in function name.
java.sql.SQLException: 'to_date' is not a recognized built-in function name.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776)
at com.splunk.dbx.sql.Database.query(Database.java:203)
at com.splunk.dbx.monitor.impl.TailDatabaseMonitor.performMonitoring(TailDatabaseMonitor.java:113)
at com.splunk.dbx.monitor.DatabaseMonitorExecutor.executeMonitor(DatabaseMonitorExecutor.java:126)
at com.splunk.dbx.monitor.DatabaseMonitorExecutor.call(DatabaseMonitorExecutor.java:102)
at com.splunk.dbx.monitor.DatabaseMonitorExecutor.call(DatabaseMonitorExecutor.java:37)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:724)

What am I doing wrong?

Thanks,
Alex

[abpe]

Indeed to_date is a Oracle function. I am connecting to MS SQL server, so I wonder if I need to do a conversion of the rising column, which is a date one. Splunk documentation says:

When your rising column is a date, make sure you wrap the checkpoint parameter in a to_date, such as: {{AND $rising_column$ > to_date(?,'YYYY-MM-DD"T"HH:MI:SS')}}. The format you use must be the same as the format that you selected.

But this example is for Oracle.
Anyway I removed the to_date statement and things seem to work.

[ziegfried · Accepted solution]

The database server returns an error message:

'to_date' is not a recognized built-in function name

Looks like the to_date function does not exist in the database server version you're using.

[abpe]

You are right, I was mistaken into thinking, that to_date was a splunk function, while actually it is a Oracle db function. I think this needs to be clarified on the DB Connect documentation.