Timestamp to date conversion with Date Variable?

[devarasu07]

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;

[tresesco]

Try doing the formatting of your variable like:

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

[rahulpawarb]

Hello Devarasu,

Please try below:

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

Regards!

Rahul

[devarasu07]

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]

Hi,

Tried that too,

script ran and result shows,

table 0 Lines fetched

[Anil_Babu_Samineni]

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

[rahulpawarb]

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]

[devarasu07]

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]

Hi Anil,

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