Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
berryandcherry6
Contributor 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
Partner
Partner

Re: Where clause with date issue in Loading

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.

11 Replies

Re: Where clause with date issue in Loading

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
Contributor II

Re: Where clause with date issue in Loading

Hi,

What is SYSDATE here?

Re: Where clause with date issue in Loading

system date which should be similar to Today()

sarvesh
Contributor III

Re: Where clause with date issue in Loading

It is system date (syntax)

arulsettu
Honored Contributor III

Re: Where clause with date issue in Loading

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

berryandcherry6
Contributor II

Re: Where clause with date issue in Loading

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

Re: Where clause with date issue in Loading

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
New Contributor III

Re: Where clause with date issue in Loading

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
Contributor II

Re: Where clause with date issue in Loading

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 ?