Home » SQL & PL/SQL » SQL & PL/SQL » subtract dates (oracle 10g Windows Xp)
subtract dates [message #433788] Fri, 04 December 2009 05:02 Go to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
There is a one column called date of birth and another constant date 31-10-2010.

I have to subtract these two dates to get the age

Thanks and regards

varosh
Re: subtract dates [message #433789 is a reply to message #433788] Fri, 04 December 2009 05:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So substract them. If you substract dates you get the difference in days.
Re: subtract dates [message #433790 is a reply to message #433789] Fri, 04 December 2009 05:10 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
i subtract already suceesfully using below code

select employee_id,firstname ,post||' '||character_certificate ,
dob,medical_fitness,trunc((CURRENT_DATE - dob) / 365,0)
from pim_emplpersonalinfo  where
trunc((CURRENT_DATE - dob) / 365,0) <35 and activecode='In Rolls' AND  post='Scientist'


Now my doubt is instead of current date , i have to use constant date 31-10-2010?

how to overcome this issue?
thanks and regards

varosh

[Updated on: Fri, 04 December 2009 05:11]

Report message to a moderator

Re: subtract dates [message #433792 is a reply to message #433790] Fri, 04 December 2009 05:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then use

to_date('31-10-2010','dd-mm-yyyy')


instead of CURRENT_DATE
Re: subtract dates [message #433793 is a reply to message #433792] Fri, 04 December 2009 05:20 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member

it raise error

    1   select employee_id,firstname ,post||' '||character_certificate ,
  2   dob,medical_fitness, to_date('31-10-2010','dd-mm-yyyy') - dob / 365
  3   from pim_emplpersonalinfo  where
  4*  to_date('31-10-2010','dd-mm-yyyy') - dob / 365 <35 and activecode='In Rolls' AND  post='Scientist'
SQL> /
 dob,medical_fitness, to_date('31-10-2010','dd-mm-yyyy') - dob / 365
                                                           *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE



thanks and regards

varosh

[Updated on: Fri, 04 December 2009 05:21]

Report message to a moderator

Re: subtract dates [message #433794 is a reply to message #433793] Fri, 04 December 2009 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If dob is a date what is the meaning of "dob / 365"?
Maybe some parenthesis are missing...

Regards
Michel

[Updated on: Fri, 04 December 2009 05:23]

Report message to a moderator

Re: subtract dates [message #433795 is a reply to message #433794] Fri, 04 December 2009 05:25 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah. So you didn't tell us the truth when you told us that "date of birth" is a date.

What datatypes are "CURRENT_DATE" and "dob" really?
Re: subtract dates [message #433799 is a reply to message #433795] Fri, 04 December 2009 05:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
On second thought, there might indeed just be the parenthesis missing, so Oracle tries the division first instead of doing the subtraction first.
Re: subtract dates [message #433806 is a reply to message #433793] Fri, 04 December 2009 05:56 Go to previous messageGo to next message
jagadeesh1985
Messages: 4
Registered: November 2009
Location: Hyderabad
Junior Member
select employee_id,firstname ,post||' '||character_certificate ,
dob,medical_fitness, (to_date('31-10-2010','dd-mm-yyyy') - dob) / 365
from pim_emplpersonalinfo where
(to_date('31-10-2010','dd-mm-yyyy') - dob) / 365 <35 and activecode='In Rolls' AND post='Scientist'

you must use the paranthesis like this
in your query
(to_date('31-10-2010','dd-mm-yyyy') - dob)/365
other wise it takes dob/365 .
As dob is a date data type we can't divide it . Try this it should work.
Re: subtract dates [message #433815 is a reply to message #433806] Fri, 04 December 2009 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to confirm what we have said but for the next time you have to know it is useless.

Regards
Michel

[Updated on: Fri, 04 December 2009 06:54]

Report message to a moderator

Re: subtract dates [message #433825 is a reply to message #433806] Fri, 04 December 2009 07:46 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
jagadeesh1985 wrote on Fri, 04 December 2009 06:56
select employee_id,firstname ,post||' '||character_certificate ,
dob,medical_fitness, (to_date('31-10-2010','dd-mm-yyyy') - dob) / 365
from pim_emplpersonalinfo where
(to_date('31-10-2010','dd-mm-yyyy') - dob) / 365 <35 and activecode='In Rolls' AND post='Scientist'

you must use the paranthesis like this
in your query
(to_date('31-10-2010','dd-mm-yyyy') - dob)/365
other wise it takes dob/365 .
As dob is a date data type we can't divide it . Try this it should work.


3 posts and none of them added any value. good work.
Re: subtract dates [message #433931 is a reply to message #433790] Sat, 05 December 2009 02:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
varosh81 wrote on Fri, 04 December 2009 12:10
trunc((CURRENT_DATE - dob) / 365,0)

Apart from all things said before, the typical way to calculate one's age in years is NOT by dividing the number of days by 365, but by calculating the number of (whole) months between two dates.
Unless you have a business requirement to use #ofDays/365, try to use trunc(months_between(current_date, dob)/12)
Re: subtract dates [message #434910 is a reply to message #433931] Fri, 11 December 2009 11:02 Go to previous message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Thanks everybody it works fine

thanks and regards

varosh
Previous Topic: How to select data(first 10 charcters) from a LONG datatype column.?
Next Topic: Crosstab query, Clob columns
Goto Forum:
  


Current Time: Thu Dec 08 06:24:08 CST 2016

Total time taken to generate the page: 0.19598 seconds