easy problem [message #7147] |
Fri, 23 May 2003 12:29 |
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 #7187 is a reply to message #7148] |
Tue, 27 May 2003 06:46 |
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
|
|
|