Ask Your Question

How to do incremental queries with TO_DATE-functions?

asked 2020-05-07 15:42:09 -0500

anonymous user


updated 2020-05-08 11:10:06 -0500

jeff gravatar image

I'm an trying to build an incremental queries for a JDBC Query Consumer, where the date column is originally a varchar. So the query is like Select TO_DATE("dayofyear"), .... from XY where TO_DATE("dayofyear",'YYYYMMDD') > TO_DATE('${OFFSET}') ORDER BY TO_DATE("dayofyear",'YYYYMMDD')

OFFSET is then 2000-01-01 and offset column dayofyear. However, I get the error "Invalid column name: dayofyear". The query with a '${OFFSET}' = '2020-01-01' works fine. I can also not put TO_DATE("dayofyear",'YYYYMMDD') as offset column.

Any idea what the problem might be?

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted

answered 2020-05-13 23:43:56 -0500

uzumaki gravatar image

You can use your varchar field as-is for OFFSET.

The invalid column name error looks like something from the database rather than StreamSets. Check if your query runs ok directly in source.

Also, you might want to look at this link

edit flag offensive delete link more


Hm, there query runs fine directly against the database. I tried to use the varchar "as is": "Select TO_DATE("dayofyear"), .... from XY where dayofyear > TO_DATE('${OFFSET}','YYYYMMDD') ORDER BY dayofyear" I still get "Invalid column name: dayofyear"

Jordi gravatar imageJordi ( 2020-07-08 14:16:54 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-05-07 15:42:09 -0500

Seen: 33 times

Last updated: May 13