Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

miranrai
New Contributor III

Date issue in where clause in SQL statement in Qlikview

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.

1 Solution

Accepted Solutions
mark6505
Valued Contributor III

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

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

4 Replies
mark6505
Valued Contributor III

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

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

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.

balrajahlawat
Esteemed Contributor

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

can you try sth like:

ABC:

Load *;

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

miranrai
New Contributor III

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

Thanks a lot for your reply..

Community Browser