topic Re: Timestamp to date conversion with Date Variable? in QlikView

Timestamp to date conversion with Date Variable?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Timestamp to date conversion with Date Variable?

tresB — Fri, 28 Apr 2017 05:36:42 GMT

Try doing the formatting of your variable like:

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

Re: Timestamp to date conversion with Date Variable?

rahulpawarb — Fri, 28 Apr 2017 05:49:08 GMT

Hello Devarasu,

Please try below:

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

Regards!

Rahul

Re: Timestamp to date conversion with Date Variable?

devarasu07 — Fri, 28 Apr 2017 05:53:34 GMT

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

Re: Timestamp to date conversion with Date Variable?

devarasu07 — Fri, 28 Apr 2017 05:55:29 GMT

Hi,

Tried that too,

script ran and result shows,

table 0 Lines fetched

Re: Timestamp to date conversion with Date Variable?

Anil_Babu_Samineni — Fri, 28 Apr 2017 05:57:11 GMT

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

Re: Timestamp to date conversion with Date Variable?

rahulpawarb — Fri, 28 Apr 2017 06:02:19 GMT

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

Re: Timestamp to date conversion with Date Variable?

devarasu07 — Fri, 28 Apr 2017 06:03:30 GMT

Re: Timestamp to date conversion with Date Variable?

devarasu07 — Fri, 28 Apr 2017 06:15:55 GMT

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

Re: Timestamp to date conversion with Date Variable?

devarasu07 — Fri, 28 Apr 2017 06:16:54 GMT

Hi Anil,

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

Re: Timestamp to date conversion with Date Variable?

rahulpawarb — Fri, 28 Apr 2017 06:37:48 GMT

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

Regards!

Rahul

Re: Timestamp to date conversion with Date Variable?

Anil_Babu_Samineni — Fri, 28 Apr 2017 06:45:59 GMT

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

Re: Timestamp to date conversion with Date Variable?

stantrolav — Fri, 28 Apr 2017 10:59:33 GMT

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