topic Re: Date formats where statement in QV. in QlikView

Date formats where statement in QV.

Anonymous — Mon, 02 Oct 2017 19:23:23 GMT

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,

Re: Date formats where statement in QV.

kaushiknsolanki — Mon, 02 Oct 2017 20:31:54 GMT

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

Re: Date formats where statement in QV.

Colin-Albert — Mon, 02 Oct 2017 20:34:45 GMT

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)' ;

Re: Date formats where statement in QV.

Anonymous — Tue, 03 Oct 2017 18:17:00 GMT

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;

Re: Date formats where statement in QV.

murugesh — Thu, 19 Dec 2019 06:40:16 GMT

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