Date formats where statement in QV.

[Anonymous]

I am trying to sql select but I am getting the following error:

SQL##f - SqlState: 37000, ErrorCode: 102, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '12'.

Invoices_SQL:

                SQL Select 'F' AS TIPO_DOCUMENTO, *

                FROM Invoices

                WHERE UpdateDate >=2017-10-02 12:57:33 and UpdateDate < 2017-10-02 19:01:25

the logic is the following...

this si SQL Server,  UPDATEDATE is date format timestamp

     // Load Tables

                $(varTableName)_SQL:

                SQL Select *

                FROM $(varTableName)

                WHERE UpdateDate >=$(vLastExecTime) and UpdateDate < $(vExecTime);

whould you please help me to guide what is the right format I should use,  tested with lot of different ways and nothing works.

Thanks in advance,

[kaushiknsolanki]

Try this.

SQL Select 'F' AS TIPO_DOCUMENTO, *

                FROM Invoices

                WHERE UpdateDate >='2017-10-02 12:57:33'  and UpdateDate < '2017-10-02 19:01:25'

Regards,

Kaushik Solanki

[Colin-Albert]

Enclose your date values in single quotes So they will be interpreted correctly by SQL.

WHERE UpdateDate >='2017-10-02 12:57:33' and UpdateDate < '2017-10-02 19:01:25'

WHERE UpdateDate >='$(vLastExecTime)' and UpdateDate < '$(vExecTime)' ;

[Anonymous]

Hi From COSTA RICA!!

Thanks so much !!  it works!  

only one more question.. 

Right at the end once process try to

store Invoices into ;

I got the following error:

Failed to open, close, or obtain file status for file D:\Qlik\QVincrementalPB\datos\Invoices.qvd

and before the load brought around 2900000 records.  is there something that I am missing?

LET vExecTime                  = timestamp(UTC(), 'YYYY-MM-DD');

LET vTodaysDate     = today();

// Load Tables

Invoices:

    SQL Select 'F' AS TIPO_DOCUMENTO, *

    FROM "SBO_WOW".dbo.OINV

    WHERE updateDate >= '$(vExecTime)' ; 

Concatenate

Load *

FROM (qvd);

//WHERE NOT EXISTS(DocEntry);   // *************************************** Change "Id" to the primary key for your tables (note all your tables must use the same name for the primary key for this script to work.

//Store the values in qvd.

store Invoices into ;

Drop Table Invoices;

[murugesh]

Can you help me i couldn't create incremental load following script. 

ODBC error

Connector reply error: SQL##f - SqlState: S0022, ErrorCode: 31750, ErrorMsg: [Simba][SQLEngine] (31750) Column not found: ModificationTime

QV_Table:
SQL SELECT *
FROM "storefront-qa2"."orders_Production_copy"
WHERE ModificationTime >= ''
AND ModificationTime < '12/19/2019 12:08:16 PM'

My Script 

Let ThisExecTime = Now( );

ODBC CONNECT TO [storefront-qa2];

QV_Table:
SQL SELECT *
FROM "storefront-qa2"."orders_Production_copy"
WHERE ModificationTime >= '$(LastExecTime)'
AND ModificationTime < '$(ThisExecTime)';

Concatenate

LOAD * FROM
[C:\Users\test\Desktop\19-12-2019\.testinc.qvd]
(qvd) WHERE NOT EXISTS(oracleOrderId);

If ScriptErrorCount = 0 then
store QV_Table into C:\Users\test\Desktop\19-12-2019\.testinc.qvd;
Let LastExecTime = ThisExecTime;
End If