OraFAQ Forum: Server Administration » difference sysdate-hiredate = yy-mm-dd.hh:mm:ss

Home » RDBMS Server » Server Administration » difference sysdate-hiredate = yy-mm-dd.hh:mm:ss

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

difference sysdate-hiredate = yy-mm-dd.hh:mm:ss [message #53311]

Thu, 12 September 2002 00:09

saif ali sabri
Messages: 20
Registered: September 2002

Junior Member

SQL> run
1 select sysdate-hiredate, current_timestamp-hiredate from emp where deptno = 10

SYSDATE-HIREDATECURRENT_TIMESTAMP-HIREDATE
-----------------------------------------

701566.576 701566 13:49:41
701727.576 701727 13:49:41
701499.576 701499 13:49:41
the result of sysdate-hiredate = No.of days.No.of seconds
the result of current_timestamp-hiredate = No.of days hh:mi:ss. just as answer time format hh:mi:ss. I want that answer of No.of days is must be yy-mm-dd as it is hh:mi:ss, not in Number of days,
I want that result must be come in yy-mm-dd.hh:mi:ss

Report message to a moderator

Re: difference sysdate-hiredate = yy-mm-dd.hh:mm:ss [message #53315 is a reply to message #53311]

Thu, 12 September 2002 04:37

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

try something like this

SQL> ed
Wrote file afiedt.buf

  1  select     trunc(months_between(sysdate,hiredate)/12) ||'-'||
  2             mod(trunc(months_between(sysdate,hiredate)),12) ||'-'||
  3     trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate)))
  4     as difference_as_yymmdd
  5* from emp
SQL> /

DIFFERENCE_AS_YYMMDD
----------------------------------------------------------------------------------------------
21-8-26
21-6-23
21-6-21
21-5-10
20-11-15
21-4-11
15-4-24
21-0-4
15-3-20
20-9-9
20-9-9

11 rows selected.

Report message to a moderator

Re: difference sysdate-hiredate = yy-mm-dd.hh:mm:ss [message #53317 is a reply to message #53311]

Thu, 12 September 2002 04:43

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

try something like this

SQL> ed
Wrote file afiedt.buf

  1  select     trunc(months_between(sysdate,hiredate)/12) ||'-'||
  2             mod(trunc(months_between(sysdate,hiredate)),12) ||'-'||
  3     trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate)))
  4     as difference_as_yymmdd
  5* from emp
SQL> /

DIFFERENCE_AS_YYMMDD
----------------------------------------------------------------------------------------------
21-8-26
21-6-23
21-6-21
21-5-10
20-11-15
21-4-11
15-4-24
21-0-4
15-3-20
20-9-9
20-9-9

11 rows selected.

Report message to a moderator

Re: difference sysdate-hiredate = yy-mm-dd.hh:mm:ss [message #53323 is a reply to message #53311]

Thu, 12 September 2002 08:09

andrew again
Messages: 2577
Registered: March 2000

Senior Member

It doesn't make sense to express a big number of days as years, months, days. A month is not 1year/12 and sometimes a year = 366 days.

Report message to a moderator

Previous Topic:	Re: Flushing the Data Buffer Cache WITHOUT BOUNCING
Next Topic:	Patitioning small sized tables , I need advice !!!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 12:13:36 CDT 2024