This discussion is archived
13 Replies Latest reply: Dec 6, 2012 6:01 AM by John Spencer RSS

Date range using dual table

652398 Newbie
Currently Being Moderated
I want to get the range of two date(two variables: e_date and s_date) in a proc code. Or I want to ensure the date range must be less than 180 days.

I used: "SELECT to_date(:e_date1, 'DDMONYY') - to_date(:s_date1, 'DDMONYY') INTO :count FROM DUAL;" and "if (count <= 180)" in a proc code.

I don't know whether there is some risks in that? Thanks.
  • 1. Re: Date range using dual table
    BluShadow Guru Moderator
    Currently Being Moderated
    Not clear what you want.

    Please post example data and expected output, as described in the FAQ: {message:id=9360002}
  • 2. Re: Date range using dual table
    Hoek Guru
    Currently Being Moderated
    I don't know whether there is some risks in that?
    A risk could be that s_date or e_date are passed in another date format then 'DDMONYY' ( by the way: why would you only use 2 digits for the year part, instead of 4?). You could consider passing the date format as a parameter as well.
  • 3. Re: Date range using dual table
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    bill wrote:
    I want to get the range of two date(two variables: e_date and s_date) in a proc code. Or I want to ensure the date range must be less than 180 days.

    I used: "SELECT to_date(:e_date1, 'DDMONYY') - to_date(:s_date1, 'DDMONYY') INTO :count FROM DUAL;" and "if (count <= 180)" in a proc code.

    I don't know whether there is some risks in that? Thanks.
    There's a risk in using 2-digit years. Use 4-digit years to reduce errors.

    There's a risk in using poor identifiers. Does the name count really reflect what the variable represents? Would days_between be more descriptive?

    There's no need to use a query here You can set variables in PL/SQL with just the := operator:
    :count := to_date (:e_date1, 'DDMONYY')
            - to_date (:s_date1, 'DDMONYY');
    The dual table is not needed very much in PL/SQL.

    If you want :count to be 180 when the number of days is 180 or greater, then you can use LEAST:
    :count := LEAST ( ( to_date (:e_date1, 'DDMONYY')
                     - to_date (:s_date1, 'DDMONYY')
                )
              , 180
              );
  • 4. Re: Date range using dual table
    BluShadow Guru Moderator
    Currently Being Moderated
    bill wrote:
    I want to get the range of two date(two variables: e_date and s_date) in a proc code. Or I want to ensure the date range must be less than 180 days.

    I used: "SELECT to_date(:e_date1, 'DDMONYY') - to_date(:s_date1, 'DDMONYY') INTO :count FROM DUAL;" and "if (count <= 180)" in a proc code.

    I don't know whether there is some risks in that? Thanks.
    You also don't need to select from dual for such things in PL/SQL procedure code. PL/SQL is perfectly capable of doing such calculations itself...
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    v_start_date DATE := date '2012-06-01';
      3    v_end_date   DATE := sysdate;
      4  begin
      5    if v_end_date - v_start_date < 180 then
      6      dbms_output.put_line('Less than 180');
      7    else
      8      dbms_output.put_line('More than 180');
      9    end if;
     10* end;
    SQL> /
    More than 180
  • 5. Re: Date range using dual table
    LPS Journeyer
    Currently Being Moderated
    Hi bill,

    In Proc(PL/SQL) no need use dual table to find the difference ,you can directly subtract two dates find the differenct and the you can use your conditional statements;


    count_diff : = e_date1 - e_date2;

    if (count_diff <= 180) Then
    ....
    else
    ----
    end if ;
  • 6. Re: Date range using dual table
    John Spencer Oracle ACE
    Currently Being Moderated
    There is no risk to that unless e_date1 and s_date1 are already dates, in which case your comparision is actually:
    to_date(to_char(:e_date1, <nls_date_format>), 'DDMONYY') - to_date(to_char(:s_date1, <nls_date_format>), 'DDMONYY')
    If e_date1 and s_date1 are strings, then you could also get errors if they are not in that format.

    You say you are doing this in a stored procedure so the biggest problem to me is the unneccesary call to the SQL engine. You can simply do the math in PL/SQL directly something like (assuming you really do need the to_date):
    If to_date(:e_date1, 'DDMONYY') - to_date(:s_date1, 'DDMONYY') <= 180 Then
       <do stuff>
    Else
       <do other stuff>
    End;
    John
  • 7. Re: Date range using dual table
    Nicosa Expert
    Currently Being Moderated
    Hi,

    Do you mean something like that ?
    SQL> l
      1  select to_date('&&begindt.','ddmmyyyy')+(level-1)
      2  from dual
      3* connect by level <= least(to_date('&&enddt.','ddmmyyyy')-to_date('&&begindt.','ddmmyyyy'),180)
    SQL> define begindt=01011970
    SQL> define enddt=01012012
    SQL> /
    old   1: select to_date('&&begindt.','ddmmyyyy')+(level-1)
    new   1: select to_date('01011970','ddmmyyyy')+(level-1)
    old   3: connect by level <= least(to_date('&&enddt.','ddmmyyyy')-to_date('&&begindt.','ddmmyyyy'),180)
    new   3: connect by level <= least(to_date('01012012','ddmmyyyy')-to_date('01011970','ddmmyyyy'),180)
    
    TO_DATE('01011970'
    ------------------
    01-JAN-70
    02-JAN-70
    03-JAN-70
    04-JAN-70
    05-JAN-70
    06-JAN-70
    07-JAN-70
    08-JAN-70
    (...)
    27-JUN-70
    28-JUN-70
    29-JUN-70
    
    180 rows selected.
    
    SQL> define enddt=17011970
    SQL> /
    old   1: select to_date('&&begindt.','ddmmyyyy')+(level-1)
    new   1: select to_date('01011970','ddmmyyyy')+(level-1)
    old   3: connect by level <= least(to_date('&&enddt.','ddmmyyyy')-to_date('&&begindt.','ddmmyyyy'),180)
    new   3: connect by level <= least(to_date('17011970','ddmmyyyy')-to_date('01011970','ddmmyyyy'),180)
    
    TO_DATE('01011970'
    ------------------
    01-JAN-70
    02-JAN-70
    03-JAN-70
    04-JAN-70
    05-JAN-70
    06-JAN-70
    07-JAN-70
    08-JAN-70
    09-JAN-70
    10-JAN-70
    11-JAN-70
    12-JAN-70
    13-JAN-70
    14-JAN-70
    15-JAN-70
    16-JAN-70
    
    16 rows selected.
  • 8. Re: Date range using dual table
    652398 Newbie
    Currently Being Moderated
    "A risk could be that s_date or e_date are passed in another date format then 'DDMONYY' ( by the way: why would you only use 2 digits for the year part, instead of 4?). You could consider passing the date format as a parameter as well."


    Because I only want to get the date range of two date(using :sdate and :edate variables), in anther words the relative value of two days, I think that 2 digits for the year part is permitted. Isn't it? Thanks.
  • 9. Re: Date range using dual table
    652398 Newbie
    Currently Being Moderated
    "There's a risk in using 2-digit years. Use 4-digit years to reduce errors."

    Because I only want to get the date range of two date(using :sdate and :edate variables), in anther words the relative value of two days, I think that 2 digits for the year part is permitted. Isn't it? Thanks.
  • 10. Re: Date range using dual table
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    bill wrote:
    "There's a risk in using 2-digit years. Use 4-digit years to reduce errors."

    Because I only want to get the date range of two date(using :sdate and :edate variables), in anther words the relative value of two days, I think that 2 digits for the year part is permitted. Isn't it? Thanks.
    Sure, it's permitted. I only said it was risky. You risk ending up with 2098 when the real year was 1998, or 0012 when the real year was 2012.
  • 11. Re: Date range using dual table
    EdStevens Guru
    Currently Being Moderated
    bill wrote:
    "There's a risk in using 2-digit years. Use 4-digit years to reduce errors."

    Because I only want to get the date range of two date(using :sdate and :edate variables), in anther words the relative value of two days, I think that 2 digits for the year part is permitted. Isn't it? Thanks.
    I can't believe Y2K was only 12 years ago and we are already forgetting the lessons.

    I few years ago (even closer to Y2k) I was in a conversation with a young programmer who expressed the opinion that Y2K was just a big hoax. After all, none of the bad things predicted really happened. I had to inform him that had he been around in 1998 and 1999 (I think he was in junior high at the time) he would have seen that the reason "nothing happened" is because armies of people like me spent two years working our a**** off to make sure nothing happened.


    Please, do yourself and your colleagues a favor and banish from your brain the entire concept of 2-digit years. When "modern" data processing got started, data records were limited to an 80-column punch card. We no longer have those constraints.
  • 12. Re: Date range using dual table
    BluShadow Guru Moderator
    Currently Being Moderated
    EdStevens wrote:
    bill wrote:
    "There's a risk in using 2-digit years. Use 4-digit years to reduce errors."

    Because I only want to get the date range of two date(using :sdate and :edate variables), in anther words the relative value of two days, I think that 2 digits for the year part is permitted. Isn't it? Thanks.
    I can't believe Y2K was only 12 years ago and we are already forgetting the lessons.

    I few years ago (even closer to Y2k) I was in a conversation with a young programmer who expressed the opinion that Y2K was just a big hoax. After all, none of the bad things predicted really happened. I had to inform him that had he been around in 1998 and 1999 (I think he was in junior high at the time) he would have seen that the reason "nothing happened" is because armies of people like me spent two years working our a**** off to make sure nothing happened.
    Yep.... I was there too... checking...re-working...testing... just to ensure that everything went ok. I even had to go in and check all the servers on New Years day (oh, yes I made sure I got paid well for that one!) to make sure they were all still up and running. Only one server had a slight issue, but it was an oldie and not fixable and not a major issue.
    Please, do yourself and your colleagues a favor and banish from your brain the entire concept of 2-digit years. When "modern" data processing got started, data records were limited to an 80-column punch card. We no longer have those constraints.
    4 digit years all the time.... oh yeah!
  • 13. Re: Date range using dual table
    John Spencer Oracle ACE
    Currently Being Moderated
    BluShadow wrote:
    EdStevens wrote:
    bill wrote:
    "There's a risk in using 2-digit years. Use 4-digit years to reduce errors."

    Because I only want to get the date range of two date(using :sdate and :edate variables), in anther words the relative value of two days, I think that 2 digits for the year part is permitted. Isn't it? Thanks.
    I can't believe Y2K was only 12 years ago and we are already forgetting the lessons.

    I few years ago (even closer to Y2k) I was in a conversation with a young programmer who expressed the opinion that Y2K was just a big hoax. After all, none of the bad things predicted really happened. I had to inform him that had he been around in 1998 and 1999 (I think he was in junior high at the time) he would have seen that the reason "nothing happened" is because armies of people like me spent two years working our a**** off to make sure nothing happened.
    Yep.... I was there too... checking...re-working...testing... just to ensure that everything went ok. I even had to go in and check all the servers on New Years day (oh, yes I made sure I got paid well for that one!) to make sure they were all still up and running. Only one server had a slight issue, but it was an oldie and not fixable and not a major issue.
    Yeah, me too. Sat in the server room all night watching the server light blink :-)
    Please, do yourself and your colleagues a favor and banish from your brain the entire concept of 2-digit years. When "modern" data processing got started, data records were limited to an 80-column punch card. We no longer have those constraints.
    4 digit years all the time.... oh yeah!
    You're just burying your head in the sand. It's time to start thinking about the Y10K problem, assuming we get through Dec. 21 :-)
    John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points