Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brijesh1991
Partner - Specialist
Partner - Specialist

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
alexandros17
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III

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
Master
Master

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
Partner - Specialist
Partner - Specialist
Author

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

Sokkorn
Master
Master

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

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
Partner - Specialist
Partner - Specialist
Author

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

er_mohit
Master II
Master II

//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

SQL SELECT *

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