CODEWITHSUNDEEP
javascriptsqldatejt400
Mardok
Mardok

Reputation: 1460

How to Query a date timestamp field with a timestamp string in sql

I am attempting to query an as400 sql database on a date field, with the jt400 npm package. On table t, mydate is a timestamp string that should equal the key, but for some reason it is not working. On a previous query I am getting the value of key. And then passing it back into this query exactly as it came out. How do I make sure the date comparison works?

const key = '123456789' // some date as a timestamp
const sql = `select * from table t where varchar_format(t.mydate, 'YYYYMMDD') = varchar_format(?, 'YYYYMMDD');`

this.pool.query(sql, [key])

Then I get the error

Error running instance method\njava.sql.SQLException: Data type mismatch. (20190525)\n\tat com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:828)\n\tat com.ibm.as400.access.JDError.throwSQLException(JDError.java:495)\n\tat com.ibm.as400.access.JDError.throwSQLException(JDError.java:464)\n\tat com.ibm.as400.access.SQLTimestamp.setTimestampFieldsFromString(SQLTimestamp.java:79)\n\tat com.ibm.as400.access.SQLTimestamp.set(SQLTimestamp.java:469)\n\tat com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:3583)\n\tat com.ibm.as400.access.AS400JDBCPreparedStatement.setString(AS400JDBCPreparedStatement.java:3231)\n\tat nodejt400.JdbcJsonClient.setParams(Unknown Source)\n\tat nodejt400.JdbcJsonClient.query(Unknown Source)\n\tat nodejt400.JT400.query(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\n

Upvotes: 1

Views: 667

Answers (1)

Mardok
Mardok

Reputation: 1460

I missed the fact that key was in the format of YYYYMMDD and varchar_format was looking for a Date, not a string.

const sql = select * from table t where varchar_format(t.mydate, 'YYYYMMDD') = ?;

Upvotes: 0

Related Questions