topic Re: Date issue in where clause in SQL statement in Qlikview in QlikView

Date issue in where clause in SQL statement in Qlikview

miranrai — Tue, 17 Nov 2015 06:33:32 GMT

Hi,

I am creating a table in qlikview using sql query. Query works fine in SQL returns records (Oracle Database) but the same query returns zero records in Qlikview. I am using variable to get '30-JUN-15' this value but it was not returning anything so tried using hard code value but still the same problem.

ABC:

Load *;

SQL Select * from xyx where TDTIME='30-JUN-15';

I tried changing date formats but its not working. I dont want to use script like as i already have huge data set.

ABC:

Load * where TDTIME='30-JUN-15';

SQL Select * from xyx;

Any help.? Thanks you.

Re: Date issue in where clause in SQL statement in Qlikview

Mark_Little — Tue, 17 Nov 2015 08:48:46 GMT

Hi,

I would probably check what the data looks like in QlikView.

Just use debug and load ten lines, Check the format of the TDTIME and then Format as needed.

Another option is to format the field something like DATE(TDTIME,'DD-MMM-YY') as TDTIME.

Hope this helps.

Mark

Re: Date issue in where clause in SQL statement in Qlikview

tamilarasu — Tue, 17 Nov 2015 09:08:18 GMT

Hi Mira,

You can also try the below one,

ABC:

Load * where TDTIME = Num(Date# ('17-Nov-2015','DD-MMM-YYYY'));

SQL Select * from xyx;

I guess, after you load the data into qlikview date fomat might changed to number format. You can also follow Mark's solution.

Re: Date issue in where clause in SQL statement in Qlikview

Anonymous — Tue, 17 Nov 2015 09:29:42 GMT

can you try sth like:

ABC:

Load *;

SQL Select * from xyx where date(TDTIME,'DD-MMM-YY')='30-JUN-15';

Re: Date issue in where clause in SQL statement in Qlikview

miranrai — Mon, 23 Nov 2015 08:04:09 GMT

Thanks a lot for your reply..