This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jul 26, 2013 7:47 AM by Greg.Spall RSS

Using trunc as difference measure btwn two dates

robleh7 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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

Legend

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