Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    3

    Unanswered: Modify daterange where end_date is null

    hello everyone,

    Ive made one or two threads in this forum and both times you guys helped me out a lot, so hopefully you will take a little of your time to help me here as well

    here is the thing:

    i have a stored procedure that takes 3 parameters

    integer ID
    timestamp startdate
    timestamp enddate

    im doing something like

    SELECT * FROM TABLE1 WHERE RECORDID = ID AND TABLEDATE >= STARTDATE AND TABLEDATE <= ENDATE

    I want to modify the query according to the value of enddate

    if the enddate is sent as a regular timestamp then

    SELECT * FROM TABLE1 WHERE RECORDID = ID AND TABLEDATE >= STARTDATE AND TABLEDATE <= ENDATE

    but if the end date is sent as null then

    SELECT * FROM TABLE1 WHERE RECORDID = ID AND TABLEDATE >= STARTDATE

    but i want to do it with an IF or CASE clause, im sure you guys know how to do this in a simple way so i would really appreciate your help thanks in advance

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Since this is a stored procedure you could do something like this: (pseudo code)

    Code:
    Preamble stuff
    
    
    declare QueryStr char(200);
    declare Query1 STATEMENT;
    
    set QueryStr = 'SELECT * FROM TABLE1 WHERE RECORDID = ID AND TABLEDATE >= STARTDATE';
    
    if EndDate is not null then
       Set QueryStr = QueryStr || ' AND TABLEDATE <= ENDDATE';
    end if;
    
    PREPARE Query1 FROM QueryStr;
    
    BEGIN 
        DECLARE c1 CURSOR FOR Query1; 
        OPEN c1; 
        FETCH c1 into sttmresult; 
       CLOSE c1; 
    END;
    
    
    Postamble stuff

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by rodrimirko View Post
    ...

    i have a stored procedure that takes 3 parameters

    integer ID
    timestamp startdate
    timestamp enddate

    im doing something like

    ...

    if the enddate is sent as a regular timestamp then

    SELECT * FROM TABLE1 WHERE RECORDID = ID AND TABLEDATE >= STARTDATE AND TABLEDATE <= ENDATE

    but if the end date is sent as null then

    SELECT * FROM TABLE1 WHERE RECORDID = ID AND TABLEDATE >= STARTDATE

    but i want to do it with an IF or CASE clause, ...
    Why do you use the names xxxxdate for timestamp data?

    Anyway,
    How about this?
    SELECT * FROM TABLE1 WHERE RECORDID = ID AND TABLEDATE BETWEEN STARTDATE AND COALESCE(ENDATE , '9999-12-31-23.59.59')

Posting Permissions

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