11 Replies Latest reply: Feb 22, 2017 7:39 AM by Supriya R

Where clause with date issue in Loading

Supriya R Feb 22, 2017 7:01 AM

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?

Re: Where clause with date issue in Loading

Sunny Talwar Feb 22, 2017 7:04 AM (in response to Supriya R)

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;
Like Show 0 Likes (0)

Actions
- Re: Where clause with date issue in Loading
  
  Supriya R Feb 22, 2017 7:06 AM (in response to Sunny Talwar )
  
  Hi,
  
  What is SYSDATE here?
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Where clause with date issue in Loading
    
    Sunny Talwar Feb 22, 2017 7:07 AM (in response to Supriya R)
    
    system date which should be similar to Today()
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Where clause with date issue in Loading
      
      Supriya R Feb 22, 2017 7:15 AM (in response to Sunny Talwar )
      
      This is giving me error when i am loading in script using SYSDATE
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Where clause with date issue in Loading
        
        Sunny Talwar Feb 22, 2017 7:16 AM (in response to Supriya R)
        
        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;
        
        Like Show 0 Likes (0)
        
        Actions
    - Re: Where clause with date issue in Loading
      
      Supriya R Feb 22, 2017 7:27 AM (in response to Sunny Talwar )
      
      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 ?
      
      Like Show 0 Likes (0)
      
      Actions
  - Re: Where clause with date issue in Loading
    
    Sarvesh Srivastava Feb 22, 2017 7:08 AM (in response to Supriya R)
    
    It is system date (syntax)
    
    Like Show 0 Likes (0)
    
    Actions
  - Re: Where clause with date issue in Loading
    
    arul settu Feb 22, 2017 7:08 AM (in response to Supriya R)
    
    SYSDATE returns the current date and time set for the operating system on which the database resides.
    
    Like Show 0 Likes (0)
    
    Actions
Re: Where clause with date issue in Loading

Sathish Babu S G Feb 22, 2017 7:23 AM (in response to Supriya R)

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;
Like Show 0 Likes (0)

Actions
Re: Where clause with date issue in Loading

Nilesh Gangurde Feb 22, 2017 7:31 AM (in response to Supriya R)

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.
Like Show 0 Likes (0)

Actions
- Re: Where clause with date issue in Loading
  
  Supriya R Feb 22, 2017 7:39 AM (in response to Nilesh Gangurde )
  
  Thanks Nilesh,
  
  For pointing out error in my code, i dint even realized it.
  
  Sorry guys for your time.
  
  Like Show 0 Likes (0)
  
  Actions

Go to original post