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: 
berryandcherry6
Creator II
Creator II

Where clause with date issue in Loading

Hi,

I want to load data where expiration date is greater than today, when i load script it doesnt filter data above expiration date greater than today(). Below shows all data.

in my databse i have date in this format 2015-02-11 23:59:59

let varExpirationDate  = Timestamp(Today(),'MM-DD-YYYY hh:mm:ss');

LOAD `campaign_id`,

     `campaign_group_id`,

     `start_date`,

      expiration_date;

SQL SELECT `campaign_id`,

     `start_date`,

     `campaign_group_id`,

      expiration_date,

FROM rcdb.CAMPAIGN where expiration_date >= '$(varExpirationDate)';

what am i missing?

1 Solution

Accepted Solutions
nilesh_gangurde
Partner - Specialist
Partner - Specialist

Hi Supriya,

See the Date format.

databse  format 2015-02-11 23:59:59

let varExpirationDate  = Timestamp(Today(),'MM-DD-YYYY hh:mm:ss');

The variable Should be :

let varExpirationDate  = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');


Hope this helps.

View solution in original post

11 Replies
sunny_talwar

May be try this:

LOAD `campaign_id`,

    `campaign_group_id`,

    `start_date`,

      expiration_date;

SQL SELECT `campaign_id`,

    `start_date`,

    `campaign_group_id`,

      expiration_date,

FROM rcdb.CAMPAIGN where expiration_date >= SYSDATE;

berryandcherry6
Creator II
Creator II
Author

Hi,

What is SYSDATE here?

sunny_talwar

system date which should be similar to Today()

sarvesh
Creator III
Creator III

It is system date (syntax)

arulsettu
Master III
Master III

SYSDATE returns the current date and time set for the operating system on which the database resides.

berryandcherry6
Creator II
Creator II
Author

This is giving me error when i am loading in script using SYSDATE

sunny_talwar

Can you run this query in TOAD or any other tool that you might use to run your queries outside of QlikView

SQL SELECT `campaign_id`,

    `start_date`,

    `campaign_group_id`,

      expiration_date,

FROM rcdb.CAMPAIGN where expiration_date >= SYSDATE;

sathishbabu_s_g
Contributor III
Contributor III

If data is less, can you try like below !!

let varExpirationDate  = Timestamp(Today(),'MM-DD-YYYY hh:mm:ss');

LOAD `campaign_id`,

     `campaign_group_id`,

     `start_date`,

      expiration_date

where expiration_date >= '$(varExpirationDate)';

SQL SELECT `campaign_id`,

     `start_date`,

     `campaign_group_id`,

      expiration_date,

FROM rcdb.CAMPAIGN;

berryandcherry6
Creator II
Creator II
Author

Below query is working

LOAD `campaign_id`,

     `campaign_group_id`,

     `start_date`,

      expiration_date;

SQL SELECT `campaign_id`,

     `start_date`,

     `campaign_group_id`,

      expiration_date

FROM rcdb.CAMPAIGN where expiration_date >= SYSDATE();

Trace($(=SYSDATE()));

but trace is not showing date ?