topic Re: issues when filtering on date column in Qlik Replicate

issues when filtering on date column

RichJ — Fri, 17 Dec 2021 02:43:50 GMT

Hi there,

I need to filter source data from SQL Server DB on a date column, but got the error:

[SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Line: 1 Column: -1 [1022502] (ar_odbc_stmt.c:2789)
00014552: 2021-12-16T18:27:54 [SOURCE_UNLOAD ]E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 16945 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The cursor was not declared. Line: 1 Column: -1 [1022502]

The filter I used is "Greater than or equal to >= '2020-01-01 00:00:00.000'", but it did not work. Then I tried to use

CONVERT(datetime, '2020-01-01 00:00:00.000') as below:

but when I click the edit, it shows:

Thanks for help,

Richard

Re: issues when filtering on date column

john_wang — Fri, 24 Dec 2021 02:38:41 GMT

Hello @RichJ ,

Noticed this article went to wrong area: Qlik Community --> Forums --> Data Analytics --> Qlik Sense --> App Development.

Can you close this one and open a new one in Replicate area? I'd like to help.

Regards,

John.

Re: issues when filtering on date column

john_wang — Thu, 30 Dec 2021 07:14:39 GMT

Hello @RichJ ,

Not very sure what's the column data type in your expression. I'm using DATE column for example:

create table testdt (id integer not null primary key, name char(20),dt date); insert into testdt values (2,'A','2021-11-02'); insert into testdt values (4,'B','2021-12-20');

Trying to get the rows which DT greater or equal than 2021-12-01, the screen copy of the demo:

If you need further assistance, please let me know the SQL how to create the demo table and prepare the demo rows, I'd like to show you how to use the filter.

Regards,

John.