Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Unanswered: how to compare DATE type

    Hi,

    How to compare to date type....

    i have created in this format in two tables ..i need to compare them..

    format is...TO_DATE('12/07/2003-23-33-44','MM/DD/YYYY-HH24-MI-SS')

    HOW TO COMPARE USING QUERY.....it returns me float value with exact no of days..but not with time...i need like this...2 days 23 hrs 33 mins 33 seconds...but not exactly like this..it should return me with all these values..is it possible.....


    pls reply me as soon as possible..

    thanx............

  2. #2
    Join Date
    Sep 2003
    Posts
    27

    Re: how to compare DATE type

    I think in 9i you can use INTERVAL datatypes.

    But here is a brute force method that you can use:

    SELECT
    a.datetime_stamp date1,
    b.datetime_stamp as date2,
    a.datetime_stamp - b.datetime_stamp as difference,
    floor(a.datetime_stamp - b.datetime_stamp) as days,
    floor ((a.datetime_stamp - b.datetime_stamp)*24) -
    floor(a.datetime_stamp - b.datetime_stamp)*24 as hours,
    floor ((a.datetime_stamp - b.datetime_stamp)*24*60) -
    floor((a.datetime_stamp - b.datetime_stamp)*24)*60 as minutes,
    floor ((a.datetime_stamp - b.datetime_stamp)*24*60*60) -
    floor((a.datetime_stamp - b.datetime_stamp)*24*60)*60 as seconds
    FROM
    test_tab a,
    test_tab b;

    DATE1 DATE2 DIFFERENCE DAYS HOURS MINUTES SECONDS
    ----------- ----------- ---------- ---------- ---------- ---------- ----------
    05-NOV-2002 04-NOV-2002 .874548611 0 20 59 21


    I am sure there are better ways than this though.

    Regards,
    Tim.

  3. #3
    Join Date
    Nov 2003
    Posts
    9

    It is possible in 8i

    Hi,
    I am using 8i..it is possible in that...


    thanx for ur reply....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •