I have added below Expression Filter on DATE column of table in source oracle database.
$TRAN_DATE <= date('now')
My target DB is SQL Server and target table column is DateTime.
Even though data exists for this filter condition, its not getting replicated to target when i run the task.
If i don't use any filter, replication works fine.
Is there any issue with this expression ?
Logs seems to indicate that, Qlik is trying to update record into target, where a new record is inserted in source DB.
Logs shows below:
Full Load:
[TRANSFORMATION ]I: Filter for Table 'INBOUND_TRANSACTION' is '$TRAN_DATE <= date('now')' (manipulator.c:1675) [TRANSFORMATION ]I: A filter is defined for column(s) 'TRAN_DATE' in table 'INBOUND_TRANSACTION' (manipulator.c:1777) [TARGET_LOAD ]I: Bulk is set to ignore max row size warnings (sqlserver_endpoint_imp.c:1758) [TARGET_LOAD ]I: Working with collated columns (sqlserver_endpoint_imp.c:1818) [TARGET_LOAD ]I: Working with BCPKeepNull set to TRUE (sqlserver_endpoint_imp.c:1822) [TARGET_LOAD ]I: Target endpoint 'SQL Server' is using provider syntax 'SQLServer' (provider_syntax_manager.c:977) [TARGET_LOAD ]I: Going to use provider syntax for datatype mapping (sqlserver_endpoint_imp.c:1849) [TARGET_LOAD ]I: Going to connect to server 'sqlmi-mcc-intell-core-uat.87327b3240b9.database.windows.net' database 'sqldb-mcc-pnc-suite-test4' (sqlserver_endpoint_imp.c:1852) [TARGET_LOAD ]I: Target endpoint 'SQL Server' is using provider syntax 'SQLServer' (provider_syntax_manager.c:977) [SOURCE_CAPTURE ]I: Source endpoint 'Oracle' is using provider syntax 'Oracle' (provider_syntax_manager.c:971) [SORTER ]I: Reload for table Id 4 is requested (sorter_transaction.c:2472) [SORTER ]I: Start collecting changes for table id = 4 (sorter_transaction.c:2347)\ [TARGET_LOAD ]I: Set timeout of Load statement to '12000' seconds (sqlserver_endpoint_imp.c:4110) [SOURCE_UNLOAD ]I: Unload finished for table 'MESSAGES' (Id = 4). 22204 rows sent. (streamcomponent.c:3804)
Incremental - Apply Changes (CDC):
[TARGET_APPLY ]I: Bulk apply operation failed. Trying to execute bulk statements in 'one-by-one' mode (bulk_apply.c:2457)
[TARGET_APPLY ]I: Applying UPDATES one-by-one for table 'BILLING_CONTENT'.'INBOUND_TRANSACTION' (1) (bulk_apply.c:5099)
[TARGET_APPLY ]W: Source changes that would have had no impact were not applied to the target database. Refer to the 'attrep_apply_exceptions' table for details (endpointshell.c:7646)
[TARGET_APPLY ]I: Switch back to bulk apply mode (bulk_apply.c:5216)
The filter is not worked as expected because Oracle DATE values include both the date and the time. The expression you are using DATE('NOW') only returns the date portion. That means records from the same day but later in the day don't get included.
In addition, DATE('NOW') is evaluated in UTC. This means the cutoff date can shift backward depending on your time zone. For example, when my local time is 2026-01-29 06:00:00 GMT+8, DATE('NOW') still returns 2026-01-28 in UTC.
You can verify this behavior by testing the filter in the Expression Builder.
Regards, Desmond
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
If you query the source table outside Replicate using this in the where clause, do you get the records you expect? You do not want to see any dates newer than now, yes?
The filter is not worked as expected because Oracle DATE values include both the date and the time. The expression you are using DATE('NOW') only returns the date portion. That means records from the same day but later in the day don't get included.
In addition, DATE('NOW') is evaluated in UTC. This means the cutoff date can shift backward depending on your time zone. For example, when my local time is 2026-01-29 06:00:00 GMT+8, DATE('NOW') still returns 2026-01-28 in UTC.
You can verify this behavior by testing the filter in the Expression Builder.
Regards, Desmond
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
