Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

brijesh1991
Contributor III

Number to Date format in where condition

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

-----

9 Replies

Re: Number to Date format in where condition

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

... where date >= '20130401'

be sure that db date is yyyymmdd

Hope it helps

calvindk
Contributor III

Re: Number to Date format in where condition

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.

Sokkorn
Honored Contributor

Re: Number to Date format in where condition

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

brijesh1991
Contributor III

Re: Number to Date format in where condition

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

Sokkorn
Honored Contributor

Re: Number to Date format in where condition

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

Not applicable

Re: Number to Date format in where condition

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

brijesh1991
Contributor III

Re: Number to Date format in where condition

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

er_mohit
Honored Contributor II

Re: Number to Date format in where condition

//My script for fetching data

Load Field1,

        Field2,

        Field3

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

hope it helps

Not applicable

Re: Number to Date format in where condition

SQL SELECT *

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

Community Browser