Criteria field in SQL Query

[rafaeloneil]

Good afternoon people,
I'm trying to create an incremental load script in QlikView but I can not get the criteria to be interpreted correctly in SQL when loading the ODBC data:

LET vHoje = Date(Today(),'YYYYMMDD');

LET vStart = Now();

LET vFileExists = IF(FileSize('C:\Users\rafael.souza1\Desktop\Apps_Rafael\BaseHeranca.QVD') > 0, -1, 0);

LET vCriterio = IF(IsNull(vCriterio), vHoje, vCriterio);

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=True;User ID=rbsouza;Data Source=DBGESTAOCP;Extended Properties="DSN=DBGESTAOCP;SERVER=pta-sybctr02;PORT=9000;UID=rbsouza;"] (XPassword is BOKHFZJMTbbKHdFMRRMGTMB);

[TabelaFONTE]:

SELECT

a.NuContrato,

a.NuCpfCnpj,

a.DtContrato,

a.CdFilial,

a.CdProduto,

a.CdModalidadeProduto,

a.CdPessoa,

a.CdOperador AS 'CdColaborador',

b.VrFinanciamento,

SUM(CASE WHEN c.FlSomaCusto = 'S' THEN c.VrCusto ELSE 0 END) AS 'VlrCusto'

FROM

DBGESTAOCP..TbContrato a,

DBGESTAOCP..TbContratoFinanceiro b,

DBGESTAOCP..TbContratoCusto c

WHERE

  a.NuContrato = b.NuContrato

AND a.NuContrato = c.NuContrato

AND a.SqContratoFinanceiroAtivo = b.SqContratoFinanceiro

AND a.SqContratoFinanceiroAtivo = c.SqContratoFinanceiro

AND b.SqContratoFinanceiro = c.SqContratoFinanceiro

AND a.DtLiquidacaoEfetiva IS NULL

AND a.DtCancelamento IS NULL

AND a.DtContrato < '$(vCriterio)'

GROUP BY a.NuContrato, a.NuCpfCnpj, a.DtContrato, a.CdFilial, a.CdProduto, a.CdModalidadeProduto, a.CdPessoa, a.CdOperador, b.VrFinanciamento;

The sql is reading the inverted variable, instead of reading 06/07/2017 is reading 07/06/2017, Can you help me?

[tomasz_tru]

Try to change SET DateFormat='YYYYDDMM' at the top of the script.

Tomasz

[rafaeloneil]

Thanks for the help, I just made the change that mentioned but it did not work

[tomasz_tru]

You changed to SET DateFormat='YYYYMMDD'?

[rafaeloneil]

I tested with 'YYYYMMDD' and with 'YYYYDDMM'

[tomasz_tru · Accepted solution]

Out of ideas Last one: when have you reloaded the app successfully? Today() would give last reload time - real today is in Today(1).

[rafaeloneil]

Thanks for your help, I managed to make it work by removing the variable date from within the IF condition:

AND a.DtContrato < CONVERT(VARCHAR(10), '$(vHoje)', 112)

Now I'll see what's missing to use the date correctly inside the variable, half way advanced