OraFAQ Forum: SQL & PL/SQL » Calculation of Day, month and year

Home » SQL & PL/SQL » SQL & PL/SQL » Calculation of Day, month and year (Oracle 11g, Window 7)

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

Calculation of Day, month and year [message #637376]

Fri, 15 May 2015 15:47

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior Member

Dear Experts.
following code is used to calculate the year and month (difference between two date)

select first_name, to_char((sysdate- hire_date) year to month) as "YY-MM"
FROM EMPLOYEES

Is there any code is available to calculate Year, Month and day also.

Regards.

Report message to a moderator

Re: Calculation of Day, month and year [message #637379 is a reply to message #637376]

Fri, 15 May 2015 15:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there any code is available to calculate Year, Month and day also.

when you subtract two DATE datatypes, the result is a NUMBER & the unit of measure is days.


  1  select first_name, to_char((sysdate- hire_date) year to month) as "YY-MM" , sysdate-hire_date DAYS_DURATION
  2  FROM EMPLOYEES
  3* where sysdate-hire_date > 7300
SQL> /

FIRST_NAME           YY-MM   DAYS_DURATION
-------------------- ------- -------------
Steven               +27-11     10194.5728
Neena                +25-08     9367.57281
Lex                  +22-04     8157.57281
Alexander            +25-04     9263.57281
Bruce                +24-00     8760.57281
Nancy                +20-09     7576.57281
Daniel               +20-09     7577.57281
Den                  +20-05     7464.57281
Alexander            +20-00     7302.57281
Payam                +20-00     7319.57281
Jennifer             +27-08     10102.5728
Susan                +20-11     7647.57281
Hermann              +20-11     7647.57281
Shelley              +20-11     7647.57281
William              +20-11     7647.57281

15 rows selected.

SQL>

you are free to present the results any way you desire

Report message to a moderator

Re: Calculation of Day, month and year [message #637380 is a reply to message #637376]

Fri, 15 May 2015 20:30

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl12c> select ename, hiredate,
  2  	    to_char ((sysdate - hiredate) year to month) || '-' ||
  3  	    to_char (trunc (sysdate - (add_months (hiredate, trunc (months_between (sysdate, hiredate))))), 'fm00')
  4  	    as "YY-MM-DD"
  5  from   emp
  6  /

ENAME      HIREDATE    YY-MM-DD
---------- ----------- -----------
SMITH      17-dec-1980 +34-05-28
ALLEN      20-feb-1981 +34-03-25
WARD       22-feb-1981 +34-03-23
JONES      02-apr-1981 +34-01-13
MARTIN     28-sep-1981 +33-08-17
BLAKE      01-may-1981 +34-00-14
CLARK      09-jun-1981 +33-11-06
SCOTT      09-dec-1982 +32-05-06
KING       17-nov-1981 +33-06-28
TURNER     08-sep-1981 +33-08-07
ADAMS      12-jan-1983 +32-04-03
JAMES      03-dec-1981 +33-05-12
FORD       03-dec-1981 +33-05-12
MILLER     23-jan-1982 +33-04-22

14 rows selected.

Report message to a moderator

Re: Calculation of Day, month and year [message #637381 is a reply to message #637376]

Sat, 16 May 2015 00:35

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

How to get the difference between 2 dates in format years/months/days

Report message to a moderator

Re: Calculation of Day, month and year [message #637397 is a reply to message #637381]

Sat, 16 May 2015 16:27

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Michel,

When I click on the link that you posted, it requires a password. When I go to the main page and try to search, it requires a password. I also don't see any obvious link to register.

Report message to a moderator

Re: Calculation of Day, month and year [message #637399 is a reply to message #637397]

Sun, 17 May 2015 01:20

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The main page is http://www.dba-village.com.
You can create your account using the menu bar Account/Register.
Direct link: http://www.dba-village.com/village/dvp_register.RegistrationForm

Maybe you could not go from the above link to the main page using the same browser window because your browser configuration does not accept redirection. For security reasons, when you failed authentication and go to another page of the site, the webmaster chooses to pass through a redirection to clear the previous environment.

Report message to a moderator

Re: Calculation of Day, month and year [message #637432 is a reply to message #637399]

Sun, 17 May 2015 13:41

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Thanks, Michel. I used the direct registration link that you provided and tried to register. Apparently, I previously registered and forgot about it, so it emailed me my password, then I tried to login and found that my account had expired due to inactivity, so it sent me another email to reactivate it. All is good now and I can see your code. That is, until the next time that I forget that I already registered there and what the password was.

Report message to a moderator

Previous Topic:	Format check on varchar check!!
Next Topic:	vpd condition with joins in DataWarehouse

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Aug 11 01:25:05 CDT 2026