Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
devarasu07
Honored Contributor II

Timestamp to date conversion with Date Variable?

Hi All,

In my DB date stored as Timestamp formate, my qvd and report just need to use date only. so i tried with floor function but getting below kind of error. do you have any idea to resolve it?

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near '10'.

SQL SELECT *

FROM TableA

Where Plant = '1000' and Date=2017-04-28 10:00:19 AM


below is my script:

MaxSOHDate:

SQL SELECT max(Date) as MaxSOHDate

FROM TableA

Where Plant = '1000';

LET vMaxSOHDate = PEEK('MaxSOHDate');

DROP TABLE MaxSOHDate;

Stock:

LOAD 

    Barcode as soh_item,

    floor(Date) as soh_date,

    SOH as soh;

SQL SELECT *

FROM TableA

Where Plant = '1000' and Date=$(vMaxSOHDate);

STORE Stock into $(vQVDPath)\Stock.QVD;

DROP table Stock;

12 Replies
MVP
MVP

Re: Timestamp to date conversion with Date Variable?

Try doing the formatting of your variable like:

LET vMaxSOHDate = Date(PEEK('MaxSOHDate') , 'YYYY-MM-DD hh:mm:ss TT' );

rahulpawarb
Valued Contributor III

Re: Timestamp to date conversion with Date Variable?

Hello Devarasu,

Please try below:

Where Plant = '1000' and Date='$(vMaxSOHDate)';

Regards!

Rahul

devarasu07
Honored Contributor II

Re: Timestamp to date conversion with Date Variable?

Hi,

I've tried the suggested way but it still getting same error.

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near '10'.

SQL SELECT *

FROM TableA

Where Plant = '1000' and Date=2017-04-28 10:00:19 AM


devarasu07
Honored Contributor II

Re: Timestamp to date conversion with Date Variable?

Hi,

Tried that too,

script ran and result shows,

table 0 Lines fetched

Re: Timestamp to date conversion with Date Variable?

May be issue here

Stock:

LOAD

    Barcode as soh_item,

    floor(Date) as soh_date,

    SOH as soh,

     Plant ;

SQL SELECT *

FROM TableA

Where Plant = 1000 and Date=$(vMaxSOHDate);

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rahulpawarb
Valued Contributor III

Re: Timestamp to date conversion with Date Variable?

Now, appending quotes fixes the error caused during reload. To fix the data availability issue follow below options:

- First check what exact value is there in TableA for field Date and make necessary changes in QlikView script to populate same value in variable vMaxSOHDate

- Secondly, convert the Date field in Where Clause by using convert function i.e. CONVERT(VARCHAR(10), Date,110) and pass value in variable vMaxSOHDate in same format.


Hope this will be helpful.


Regards!

Rahul

devarasu07
Honored Contributor II

Re: Timestamp to date conversion with Date Variable?

error.png

devarasu07
Honored Contributor II

Re: Timestamp to date conversion with Date Variable?

Hi,

Tried with timestamp to date conversion sql max select query itself but result still retuns 0 records --  (my SQL have

data)

if we use convert function then it's changed to varchar format and returns 0

Thanks,

Deva

devarasu07
Honored Contributor II

Re: Timestamp to date conversion with Date Variable?

Hi Anil,

Plant no issue, i tried remove plant condition and re-ran the script and getting error only. any other suggestions. Tks