SQL Connector Error - Where clause with timestamp

[timpoismans]

Hi all

I've been banging my head on this for a few hours, but I can't seem to understand why my script is throwing the following error:

The following error occurred:
Connector reply error: ErrorSource: Microsoft OLE DB Provider for
SQL Server, ErrorMsg: The conversion of a varchar data type to a datetime data type
resulted in an out-of-range value.

My code is as follows:

tblWebPageLog:
LOAD
    WebPageLogID,
    "UserID",
    UserName,
    UserCompany 				as %CompanyKey,
    UserLocation,
    PageAccessTime,
    PageName,
    [if statement based on pagename to determine Domain] as Domain,
    [if statement based on pagename to determine Page] as Page,
    PageAction,
    PageData;
SQL SELECT WebPageLogID,
    "UserID",
    UserName,
    UserCompany,
    UserLocation,
    PageAccessTime,
    PageName,
    PageAction,
    PageData
FROM "TNT_PP".dbo.tblWebPageLog with (nolock)
WHERE PageAccessTime >= '$(vReloadTime)'
;

And the variable vReloadTime is created as such:

LET vReloadTime = Timestamp(Num(Peek('MaxDate', 0, 'Max')),'DD-MM-YYYY hh:mm:ss');

When tracing the variable, it gives the following output:

29-01-2019 10:07:50

Thanks in advance!

Regards,

Tim P.

[pradosh_thakur]

1: change both the timestamp to number and compare
2: make sure the fields in preceding load are present in SQL load from data base.

[marcus_malinow · Accepted solution]

This looks like it could be an interpretation issue. The localisation of your database may be US. You could try changing the timestamp format to 'YYYY-MM-DD hh:mm:ss' as this will be interpreted correctly regardless of locale.

[timpoismans]

Thank you very much!

I checked the format of the field before reloading the app, but apparently when Qlik shows you the "Data preview" when selecting data to load, it already uses the set formats for certain fields.
Kinda sucks that you can't see the original format of the field in the database then.