Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
miranrai
Contributor III
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
Mark_Little
Luminary
Luminary

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

View solution in original post

4 Replies
Mark_Little
Luminary
Luminary

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

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
Not applicable

can you try sth like:

ABC:

Load *;

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

miranrai
Contributor III
Contributor III
Author

Thanks a lot for your reply..