1 2 Previous Next 16 Replies Latest reply: Jul 26, 2013 9:47 AM by Greg Spall RSS

    Using trunc as difference measure btwn two dates

    robleh7

      I have a SQL query that works smoothly. It takes the difference between sets of dates that are >=0 and <=0. here it is:

       

      SELECT createdate,  id, objid, start_date, end_date

      FROM greggs_date, HNEMap

      where active =1

      and trunc (createdate-start_date) >=0  and trunc (createdate-end_date) <=0;

       

      Problem is I can't output the differences. In MS SQL Server you can show the number of days difference with the datediff function named in the SELECT part of the SQL statement not so with trunc used to get date differences. How can this query be rework to show the actual days of difference between start and end from create date.

        • 1. Re: Using trunc as difference measure btwn two dates
          SomeoneElse

          TRUNC has nothing to do with getting "date differences".

           

          In Oracle there's no need for a datediff function.  Just subtract one date from another.  The difference is expressed in days (including a fractional part if necessary).

          • 2. Re: Using trunc as difference measure btwn two dates
            Frank Kulash

            Hi,

             

             

             

            robleh7 wrote:

             

            I have a SQL query that works smoothly. It takes the difference between sets of dates that are >=0 and <=0. here it is:

             

            SELECT createdate,  id, objid, start_date, end_date

            FROM greggs_date, HNEMap

            where active =1

            and trunc (createdate-start_date) >=0  and trunc (createdate-end_date) <=0;

             

            Problem is I can't output the differences. In MS SQL Server you can show the number of days difference with the datediff function named in the SELECT part of the SQL statement not so with trunc used to get date differences. How can this query be rework to show the actual days of difference between start and end from create date.

             

            Sorry, I don't understand the problem.

            TRUNC (createdate - startdate)    is just a NUMBER.  You can display it just like any other NUMBER.

             

            SELECT  k.hiredate                         AS base_date

            ,       d30.hiredate

            ,       TRUNC (k.hiredate - d30.hiredate)  AS dif

            FROM        scott.emp  k

            CROSS JOIN  scott.emp  d30

            WHERE   k.ename        = 'KING'

            AND     d30.deptno     = 30

            ;

             

            Output:

             

            BASE_DATE   HIREDATE           DIF

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

            17-Nov-1981 20-Feb-1981        270

            17-Nov-1981 22-Feb-1981        268

            17-Nov-1981 28-Sep-1981         50

            17-Nov-1981 01-May-1981        200

            17-Nov-1981 08-Sep-1981         70

            17-Nov-1981 03-Dec-1981        -16

             

             

            Post a complete test script (including CREATE TABLE and INSERT statements, if needed)  and the results you want from that script.

            • 3. Re: Using trunc as difference measure btwn two dates
              robleh7

              How? if I put this in the select part of the statement as manner of ora errors in ensue. I know I can just subtract dates rather than use the trunc function but I want to output the difference of the subtraction. How can I show this. A simple line of code would suffice.

              • 4. Re: Using trunc as difference measure btwn two dates
                SomeoneElse

                D1 and D2 are both DATE types

                 

                SQL> select * from t;

                D1                   D2
                -------------------- --------------------
                25-JUL-2013 14:22:03 26-NOV-2013 01:10:03

                 

                SQL> select d2 - d1 from t;

                 

                               D2-D1
                --------------------
                              123.45

                 

                If you don't want the time portion of the date to be considered, you can use TRUNC to set it to zeros (well, midnight).

                 

                SQL> select trunc(d2) - trunc(d1) from t;

                TRUNC(D2)-TRUNC(D1)
                --------------------
                                 124

                • 5. Re: Using trunc as difference measure btwn two dates
                  Frank Kulash

                  Hi,

                   

                  Sorry, I still don't understand.  You really need to post a complete script, including your code (even if it gets errors), and whatever data it needs to run (if you can't use commonly available tables like scott.emp).

                   

                  If you have a NUMBER column, such as empno, would you know how to dispaly that column?

                  You can dispaly any other NUMBER, such as the NUMBER returned by TRUNC (createdate - start_date) the same way.  Just replace

                   

                  empno

                   

                  with

                   

                  TRUNC (createdate - start_date)

                  • 6. Re: Using trunc as difference measure btwn two dates
                    Greg Spall

                    Please do 1 of the following:

                     

                    1) Show results of following 2 commands:

                    desc greggs_date

                    desc HNEMap

                     

                    or

                     

                    2) create a small, complete sample showing the error you're getting and that we can run at our end.

                    • 7. Re: Using trunc as difference measure btwn two dates
                      robleh7

                      Thx everybody but I'm starting to get there thx to someoneelse and others. I have difference just have to tweek them.

                      stay tuned

                      • 8. Re: Using trunc as difference measure btwn two dates
                        robleh7

                        Okay Greg here the modify query I've made the RUNS and get differences.

                        SELECT createdate, objid, id,TransactionDtTm,

                        trunc (createdate- start_date)  as difference,

                        trunc (createdate- end_date)  as difference1

                        FROM HNEMap, greggs_date;

                         

                        But I want it to get difference >=0 in both cases.  The moment I stick >=0 after the difference truncated I get ORA 923 from keyword not found where expected etc. you know the error I'm sure.

                        • 9. Re: Using trunc as difference measure btwn two dates
                          robleh7

                          Oops Greg call me an idiot I had the dates reversed of course they are not negative. But I do what them not in the thousands. i know I get do some formatting the trunc right?

                          • 10. Re: Using trunc as difference measure btwn two dates
                            Greg Spall

                            2) create a small, complete sample showing the error you're getting and that we can run at our end.

                            • 11. Re: Using trunc as difference measure btwn two dates
                              John Spencer

                              robleh7 wrote:

                               

                              Okay Greg here the modify query I've made the RUNS and get differences.

                              SELECT createdate, objid, id,TransactionDtTm,

                              trunc (createdate- start_date)  as difference,

                              trunc (createdate- end_date)  as difference1

                              FROM HNEMap, greggs_date;

                               

                              But I want it to get difference >=0 in both cases.  The moment I stick >=0 after the difference truncated I get ORA 923 from keyword not found where expected etc. you know the error I'm sure.

                              That is what a where clause is for.  You had a where clause on your first example add it to the last query you posted.

                               

                              As a side note, unless one of the two tables in you query has only a single row, you are doing a cartesian join betwee nthe two.  You might want to consider a join clause as well.

                               

                              John

                              • 12. Re: Using trunc as difference measure btwn two dates
                                robleh7

                                Actually, I don't want a cartesian join. But I've since done an iteration of this query. It's below.

                                 

                                SELECT createdate, start_date, end_date,TransactionDtTm, id, objid

                                FROM greggs_date, hnemap

                                where active =1

                                AND trunc (start_date -createdate) <= 0

                                and trunc (end_date - createdate) >= 0 ;

                                What I want to show, which this one does not, is to show the difference of the two trunc above. There are 735 rec on this query and all the dates are in 2013. The last piece is to subtract the two.

                                Something like trunc (start_date -createdate) <= 0  -trunc (end_date - createdate) >= 0

                                But I know that won't work.

                                • 13. Re: Using trunc as difference measure btwn two dates
                                  EdStevens

                                  robleh7 wrote:

                                   

                                  Actually, I don't want a cartesian join. But I've since done an iteration of this query. It's below.

                                   

                                  SELECT createdate, start_date, end_date,TransactionDtTm, id, objid

                                  FROM greggs_date, hnemap

                                  where active =1

                                  AND trunc (start_date -createdate) <= 0

                                  and trunc (end_date - createdate) >= 0 ;

                                  What I want to show, which this one does not, is to show the difference of the two trunc above. There are 735 rec on this query and all the dates are in 2013. The last piece is to subtract the two.

                                  Something like trunc (start_date -createdate) <= 0  -trunc (end_date - createdate) >= 0

                                  But I know that won't work.

                                   

                                  I think part of your problem is you keep insisting on applying the TRUNC function to the number that results from subtracting one date from another.  You were shown the (probably) correct format of applying the TRUNC to the dates before subtracing them:

                                   

                                  select create_date,

                                         start_date,

                                         end_date,

                                         trunc(end_date) - trunc(start_date) as days_diff

                                  from .....

                                  where ....

                                  and trunc(end_date) - trunc(start_date) > 0

                                  • 14. Re: Using trunc as difference measure btwn two dates
                                    Greg Spall

                                    2) create a small, complete sample showing the error you're getting and that we can run at our end.


                                    Seriously .. do this, and we'll have an answer for you in just a few minutes, rather this back and forth guess-work and confusion.

                                    We don't know what you need so we can't help you ...


                                    1 2 Previous Next