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;

[rahulpawarb]

Have you tried first approach? Populating value in QlikView variable same as database value.

Regards!

Rahul

[Anil_Babu_Samineni]

Can you share few lines with inline data and then we will check the same, What will be the happen

[stantrolav]

1. Maybe you need to Floor() your result in variable?

-Ok. You Tried it already

2. Lets make date again using text:

LET vMaxSOHDate = Date#(left(PEEK('MaxSOHDate'), 10), 'YYYY-MM-DD');

3. After this interprete your new variable as text to SQL syntacsis.

Stock:

LOAD

    Barcode as soh_item,

    floor(Date) as soh_date,

    SOH as soh;

SQL SELECT *

FROM TableA

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