6 Replies Latest reply on Oct 10, 2017 3:35 PM by rp0428

    Date manipulation

    vga

      Hi everyone,

       

      I'm making a query that's supposed to pull out the data between previous day 1900 hours and current day 18:59:59 hours.

      This query will be added in a report that get's refreshed on a daily basis but at different hours so can't use sysdate - 1.

      I'm using oracle sql developer  Version 4.0.1.14.

       

      Thanks in advance!

        • 1. Re: Date manipulation
          thatJeffSmith-Oracle

          Good luck!

           

          I would advise you to upgrade your SQL Developer first. Version 4.0.1 is quite old.

          • 2. Re: Date manipulation
            vga

            Hi Jeff,

             

            Unfortunately this is not up to me. Company rules.

            For every update we need to get in touch with the IT department and that takes ages.

            Any hints/tips to help me with my task?

            Regards,

            George

            • 3. Re: Date manipulation
              BPeaslandDBA

              This is not a SQL Dev question, but more a SQL question, the SQL language, not the SQL Dev product. This might be a better space for this question: SQL & PL/SQL

               

              19:00 hours yesterday is midnight today minus 5 hours. So this gives me 19:00 yesterday: TRUNC(sysdate)-5/24 and an example is here:

               

              SQL> SELECT to_char(sysdate,'MM/DD HH24:MI') AS curr_time,

                2  to_char(trunc(sysdate)-5/24,'MM/DD HH24:MI') as yesterday_1900

                3  FROM dual;

               

               

              CURR_TIME  YESTERDAY_1

              ----------- -----------

              10/10 09:26 10/09 19:00


              that works because there are 24 hours in a day and 5 hours is 5/24.

               

               

              If you want 23:59 more than 19:00 yesterday, you just add that much of a day. In this case, there are 1440 minutes in day and i want to add 1/1440:

               

              SQL> SELECT to_char(sysdate,'MM/DD HH24:MI') AS curr_time,

                2  to_char(TRUNC(sysdate)-5/24,'MM/DD HH24:MI') AS yesterday_1900,

                3  to_char(TRUNC(sysdate)-5/24+1-1/1440,'MM/DD HH24:MI') AS today_1859

                4  from dual;

               

               

              CURR_TIME   YESTERDAY_1 TODAY_1859

              ----------- ----------- -----------

              10/10 09:31 10/09 19:00 10/10 18:59

               

               

              Cheers,
              Brian

              • 4. Re: Date manipulation
                thatJeffSmith-Oracle

                Not on this forum. And if you post in on the SQL & PL/SQL forum you're not likely to get much help either, not without a lot more details. Like, what your table/data looks like. You should also try to post your best attempt at the SQL first. No one there is going to be motivated to write it for you.

                 

                 

                As for your IT department - I don't want you to get in trouble with them. But, there is no installer here. Just grab the zip, unzip it to your desktop, and go. Otherwise you're on yesterday's tech, if yesterday was 2013.

                1 person found this helpful
                • 5. Re: Date manipulation
                  vga

                  Thanks a lot Jeff!

                  • 6. Re: Date manipulation
                    This query will be added in a report that get's refreshed on a daily basis but at different hours so can't use sysdate - 1.

                    So use 'TRUNC(sysdate) - 1'.

                     

                    If you need more help mark this thread ANSWERED and repost the question in the sql and pl/sql forum.