Incremental Load - date format

[Anonymous]

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

[sasiparupudi1]

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

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

[Anonymous]

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?

[sasiparupudi1 · Accepted solution]

‌hi

My bad.

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