Where clause with Date comparison in SAP Connector

[micheledenardi]

Hi expert,

i'm using Qlik Sap connector to extract some data and want to extract all the record that AEDAT is major than yesterday.

I have already tried to this solution

SQL SELECT *

FROM  TABLE

WHERE AEDAT >= 20171001;

and also

SQL SELECT *

FROM  TABLE

WHERE AEDAT >= '20171001';

and also

SQL SELECT *

FROM  TABLE

WHERE AEDAT >= '01/10/2017';

and also

SQL SELECT *

FROM  TABLE

WHERE AEDAT >= 01/10/2017;

but it doesn't work because AEDAT is stored with DD/MM/YYYY format and i don't know how to compare AEDAT with yesterday date in order to obtain all record with AEDAT >= yesterday.

Any ides ?

[trm]

Hi Michele,

The following works for me:

// SAP uses the format 20171001 for dates

// Subtracting 1 is the same as subtracting 1 day, since there's no yesterday() function in QV.

LET date = Date(Today(1) - 1 , 'YYYYMMDD');

[VBAK]:

SELECT AUDAT // Doc. Date

FROM VBAK

WHERE AUDAT >=  '$(date)';

// Make sure to quote the date

Regards,

// Thomas Örnmarker

SAP Connectors Developer

[Hakan_Ronningberg · Accepted solution]

Hi Michele,

Is the field AEDAT of data type DATS? If so it should be written like:

SQL SELECT * FROM  TABLE WHERE AEDAT >= '20171001';

SAP stores dates like '20171001' so the user setting for displaying dates should not matter.

You can test the statement in the transaction n/QTQVC/SQL in SAP. Online help:

SAP SQL Connector user configuration ‒ Qlik Connectors

Regards,

Hakan