topic Re: Number to Date format in where condition in QlikView

Number to Date format in where condition

brijesh1991 — Thu, 23 May 2013 08:03:06 GMT

Hi all,

I wanted to fetch a table from my backend and wanted to generate a qvd, but I wanted data from 1st Aprl,2013 only.
Due to my date in backend is in number format, I am getting a script level error which is attached here.

------

//My script for fetching data

Load Field1,

Field2,

Field3

from "dbname"."tablename" where Date >='2013-04-01';

-----

Re: Number to Date format in where condition

alexandros17 — Thu, 23 May 2013 08:11:25 GMT

Check data format on server and use it without - or / so:

... where date >= '20130401'

be sure that db date is yyyymmdd

Hope it helps

Re: Number to Date format in where condition

calvindk — Thu, 23 May 2013 08:16:31 GMT

Convert your numeric date to date format or convert your string date to numeric..

Something like

//My script for fetching data

Load Field1,

Field2,

Field3

from "dbname"."tablename" where Cast(Date as Date, 'YYYY-MM-DD') >='2013-04-01';

Oh and just to clarify. That error is not a Qlikview script error. That is an SQL error.

Re: Number to Date format in where condition

Sokkorn — Thu, 23 May 2013 08:25:29 GMT

Hi Brijesh,

Base on my experient, Sybase store date like this 20130401 it is correct?

Now let try:

[Date]:

LOAD

Field1,

Field2,

Field3,

TRDT Where Date(Date#(TRDT,'YYYYMMDD'),'YYYY-MM-DD') >= '2013-04-01';

Select * from "dbname"."tablename";

Regards,

Sokkorn

Re: Number to Date format in where condition

brijesh1991 — Thu, 23 May 2013 09:03:26 GMT

Thanks for your efforts, but it is giving me an error like "Adaptive Server Anywhere]Wrong number of parameters to function 'Date'"

Re: Number to Date format in where condition

Sokkorn — Thu, 23 May 2013 09:09:08 GMT

Hi,

[Date]:
LOAD
    Field1,
    Field2,
    Field3,
    TRDT Where Date(Date#(TRDT,'YYYYMMDD'),'YYYY-MM-DD') >= '2013-04-01';
Select * from "dbname"."tablename";

Above script should work. Can you share your script?

Regards,

Sokkorn

Re: Number to Date format in where condition

Thu, 23 May 2013 09:10:27 GMT

try this

Load Field1,

Field2,

Field3

from "dbname"."tablename" where Date >=date('2013-04-01','YYYY-MM-DD');

****************************************************************

OR TRY THIS

Load Field1,

Field2,

Field3

from "dbname"."tablename" where Date >=num(date('2013-04-01','YYYY-MM-DD'));

Re: Number to Date format in where condition

brijesh1991 — Thu, 23 May 2013 09:13:40 GMT

Thanks alexandros17 for your efforts, but it is giving ODBC read failed error. . .

Re: Number to Date format in where condition

er_mohit — Thu, 23 May 2013 09:18:24 GMT

//My script for fetching data

Load Field1,

Field2,

Field3

from "dbname"."tablename" where Date(Datefield,'YYYY-MM-DD')' >='2013-04-01';

hope it helps

Re: Number to Date format in where condition

Thu, 23 May 2013 09:24:16 GMT

SQL SELECT *

from ....... ...... ..... where TRDT >= num(date('2013-04-01'));