Home » SQL & PL/SQL » SQL & PL/SQL » easy problem
easy problem [message #7147] Fri, 23 May 2003 12:29 Go to next message
tuster
Messages: 2
Registered: May 2003
Junior Member
hi i m a new one .i just want to now how can we subtract one date from others one.
Re: easy problem [message #7148 is a reply to message #7147] Fri, 23 May 2003 13:42 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Just subtract them - you will get a numeric value (number of days and fraction of a day) as a result.
Re: easy problem [message #7156 is a reply to message #7148] Sat, 24 May 2003 23:14 Go to previous messageGo to next message
tuster
Messages: 2
Registered: May 2003
Junior Member
no i want the format like this
25/05/02 - 25/05/80 the result will be
00/00/22 in pl/sql code
Re: easy problem [message #7187 is a reply to message #7148] Tue, 27 May 2003 06:46 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
SQL> SELECT TO_CHAR(d1,'fmDy DD/MM/YYYY')                        d1
  2  ,      TO_CHAR(d2,'fmDy DD/MM/YYYY')                        d2
  3  FROM   t
  4  /
  
D1             D2
-------------- --------------
Sat 1/1/2000   Sat 1/1/2000
Sat 1/1/2000   Tue 1/2/2000
Sat 1/1/2000   Thu 2/3/2000
Sat 1/1/2000   Mon 3/4/2000
Sat 1/1/2000   Thu 4/5/2000
Sat 1/1/2000   Mon 5/6/2000
Sat 1/1/2000   Tue 27/5/2003
Sat 1/1/2000   Tue 1/1/2019
  
8 rows selected.
  
SQL> SELECT TO_CHAR(d1,'fmDy DD/MM/YYYY')                        d1
  2  ,      TO_CHAR(d2,'fmDy DD/MM/YYYY')                        d2
  3  ,      (GREATEST(d1,d2) - <a href="http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions5a.htm#SQLRF00603">ADD_MONTHS</a>(LEAST(d1,d2),mo_diff)) days
  4  ,      MOD(mo_diff,12)                                      mos
  5  ,      TRUNC(mo_diff/12)                                    yrs
  6  ,      TO_CHAR((GREATEST(d1,d2)
  7                  -
  8                  ADD_MONTHS(LEAST(d1,d2),mo_diff))
  9         ,       'fm00')
 10         || '/'
 11         || TO_CHAR(MOD(mo_diff,12)
 12            ,       'fm00')
 13         || '/'
 14         || TO_CHAR(TRUNC(mo_diff/12)
 15            ,       'fm0000')                                 "DIFF (DD/MM/YYYY)"
 16  FROM  (SELECT d1
 17         ,      d2
 18         ,      TRUNC(ABS(<a href="http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions73a.htm#SQLRF00669">MONTHS_BETWEEN</a>(t.d2,t.d1))) mo_diff
 19         FROM   t)
 20  /
  
D1             D2                   DAYS        MOS        YRS DIFF (DD/MM/Y
-------------- -------------- ---------- ---------- ---------- -------------
Sat 1/1/2000   Sat 1/1/2000            0          0          0 00/00/0000
Sat 1/1/2000   Tue 1/2/2000            0          1          0 00/01/0000
Sat 1/1/2000   Thu 2/3/2000            1          2          0 01/02/0000
Sat 1/1/2000   Mon 3/4/2000            2          3          0 02/03/0000
Sat 1/1/2000   Thu 4/5/2000            3          4          0 03/04/0000
Sat 1/1/2000   Mon 5/6/2000            4          5          0 04/05/0000
Sat 1/1/2000   Tue 27/5/2003          26          4          3 26/04/0003
Sat 1/1/2000   Tue 1/1/2019            0          0         19 00/00/0019
  
8 rows selected.
  
SQL> 
HTH,

A
Previous Topic: TABLE
Next Topic: Whether Windows XP Prof. accepts Oracle 9i Enterprise
Goto Forum:
  


Current Time: Thu Mar 28 03:54:54 CDT 2024