13 Replies Latest reply: Dec 6, 2012 8:01 AM by John Spencer RSS

    Date range using dual table

    652398
      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
          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
            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
              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
                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
                  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
                    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-Oracle
                      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
                        "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
                          "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
                            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
                              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
                                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
                                  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