Date issue in where clause in SQL statement in Qlikview

[miranrai]

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.

[Mark_Little · Accepted solution]

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

[tamilarasu]

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.

[Anonymous]

can you try sth like:

ABC:

Load *;

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

[miranrai]

Thanks a lot for your reply..