SSIS: Loading a datetime from Oracle

Imagine an Integration Services package where you need to load data from an Oracle database.  Typical for ETL you try to use a timestamp field in the source table to determine if there were any changes since last load.  Let’s say that field is called LAST_MODIFICATION.  The query that you’re using to load the data is parameterized and the value passed in is the highest timestamp of the previous Load (or should I say Extract?).  So your query may look something like this:

In this query the question mark gets replaced with a datetime value, for instance ‘2009/08/27 21:26:32’.  (Yeah, I know, you shouldn’t select star but that’s not the point of this post.)

When running your package you suddenly get the following error:

ORA-01849: hour must be between 1 and 12

After some cursing and trial and error, you decide to have a closer look at Oracle’s to_date() function.  And there is your answer: HH returns the same as HH12, an hour between 1 and 12.  Which is not what we want here, we want hours starting at 1 and ending at 23.  So we should use HH24 instead.  A correct query would be:

SELECT * FROM THE_SCHEMA.THE_TABLE
WHERE LAST_MODIFICATION > to_date(?, ‘yyyy/mm/dd hh24:mi:ss’)

Another lesson learned… (and noted in case I need it again )