topic Re: Incremental Load - date format in QlikView

Incremental Load - date format

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I trying to load my data by using incremental method.

by using this kind of code:

RefTime=reloadtime();

_________

SQL SELECT

FROM

WHERE UpdateDate>'$(RefTime)'

_________

Load

FROM

WHERE

NOT exists(id)

_________

The problem is when I use the variable the where condition not working - only data from my initial load is stored.

When I am not using the variable and just write the time (for example 6/9/2016) the incremental load work perfect.

The format date of UpdateDate (as in the DB) is "YYYY-MM-DD HH:MM:SS"

RefTime is as QlikView format "M/D/YYYY HH:MM:SS"

I can't figure it out how i need to change the format date so that query will run.

Thanks for any help

Re: Incremental Load - date format

sasiparupudi1 — Thu, 09 Jun 2016 13:43:10 GMT

change the variable value from "M/D/YYYY HH:MM:SS" to

SET TimestampFormat='YYYY-MM-DD TT hh:mm:ss';

Re: Incremental Load - date format

Anonymous — Thu, 09 Jun 2016 14:58:06 GMT

Hi Thanks for your reply.

from some reason i now get this error

SQL##f - SqlState: 22008, ErrorCode: 242, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I also see in the error description this date format

UpdateDate>'2016-06-09 PM 05:51:45'

may the "PM" is the reason that the script failed?

Re: Incremental Load - date format

sasiparupudi1 — Thu, 09 Jun 2016 18:36:49 GMT

‌hi

My bad.

the format string should be YYYY-MM-DD hh:mm:ss . The TT was a typo