Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22

    Question Unanswered: SQL string for blank or populated date

    What I am trying to achieve is if STARTDATE is today or after today and ENDDATE is not blank and ENDDATE is greater than or equal to today select the data.
    Also if ENDDATE is blank select the data.
    Code:
    SELECT ColumnData FROM MyTable WHERE STARTDATE >= current_date AND (!(ENDDATE IS NOT NULL AND ENDATE >= current_date))
    Does this make any sense? If not please tell me how I should code this.

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    SELECT ColumnData 
    FROM MyTable 
    WHERE STARTDATE >= TRUNC(current_date) AND 
          NVL(ENDATE, current_date+1) >= TRUNC(current_date);
    --=cf

  3. #3
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22

    SQL string for blank or populated date

    Thank you for your reply but I fail to understand the
    NVL(ENDATE, current_date+1) >= TRUNC(current_date);
    Could you please explain in english?

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    The NVL() function accepts 2 values. If the first value is null, then it returns the second value, otherwise it returns the first

    Code:
    SQL> create table t (f number);
    
    Table created.
    
    SQL> insert into t values (1);
    
    1 row created.
    
    SQL> insert into t values (null);
    
    1 row created.
    
    SQL> select nvl(to_char(f), 'I am null') from t;
    
    NVL(TO_CHAR(F),'IAMNULL')
    ----------------------------------------
    1
    I am null
    In your case, instead of 'I am null', I'm having it return tomorrow's date, so that when the data value is null, it qualifies as a record that could be returned. I guess you could also write is as:

    Code:
    SELECT ColumnData 
    FROM MyTable 
    WHERE STARTDATE >= TRUNC(current_date) AND 
          (ENDATE is null or ENDATE >= TRUNC(current_date));

  5. #5
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22

    SQL string for blank or populated date

    Thank you. I love programming for just this reason. No matter how much you think that you know there is always something new to learn.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •