Home » SQL & PL/SQL » SQL & PL/SQL » Calculating date difference when inserting..
Calculating date difference when inserting.. [message #209763] Sun, 17 December 2006 13:13 Go to next message
avion
Messages: 11
Registered: December 2006
Junior Member
Hi there,

I'm new to oracle and I've been browsing the website and forums which have helped me alot with my learning.

I'm trying to figure out how to insert a calculated column, which calculates the difference from the current date and another date already in another column..

For example: I have 2 columns - 'Date_of_Birth', and another called 'Days_from_Birth' which will calculate the difference from the current system date and the Date_of_Birth dates inserted.

This is what I have at the moment:

CREATE TABLE mytable
(
id int not null,
date_of_birth date,
days_from_birth int,
primary key (id));


insert into mytable values 
('',to_date('10/12/1995', 'dd/mm/yyyy'), <CALCULATION GOES HERE??>);


I've searched 'datediff' on these forums, and it seems to do something very similar to what I want, but its used in SELECT statements. I want the calculations done in INSERT. Basically the values will always change every day.

Is this possible?, any advice appreciated.

Thanks Smile
Re: Calculating date difference when inserting.. [message #209764 is a reply to message #209763] Sun, 17 December 2006 13:43 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member




CREATE TABLE foom
(
id int not null,
date_of_birth date,
days_from_birth int,
primary key (id));


insert into foom values (1,
to_date('10/12/1995', 'dd/mm/yyyy'),
extract( day from ( numtodsinterval(sysdate-
to_date('10/12/1995', 'dd/mm/yyyy'),'DAY') ))
)
bspdb>/

1 row created.

bspdb>commit;

Commit complete.

bspdb>select * from foom;


ID DATE_OF_BIRTH DAYS_FROM_BIRTH
---------- ------------------- ---------------
1 1995-12-10 00:00:00 4025





Srini
Re: Calculating date difference when inserting.. [message #209833 is a reply to message #209763] Mon, 18 December 2006 01:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You can subtract your date from sysdate to get the number of days. Trunc it to get a whole number:
SQL> create table faq (dob date, days number);

Table created.

SQL> insert into faq
  2  ( dob
  3  , days
  4  ) values
  5  ( to_date('01-01-1960', 'dd-mm-yyyy')
  6  , trunc(sysdate - to_date('01-01-1960', 'dd-mm-yyyy'))
  7  )
  8  /

1 row created.

SQL> select * from faq;

DOB             DAYS
--------- ----------
01-JAN-60      17153

BUT... I would advise to store the sysdate itself instead of some derivated value that you can always calculate if needed.

[Updated on: Mon, 18 December 2006 01:06]

Report message to a moderator

Re: Calculating date difference when inserting.. [message #209907 is a reply to message #209763] Mon, 18 December 2006 06:28 Go to previous message
avion
Messages: 11
Registered: December 2006
Junior Member
Thanks for the help, it works Smile
Previous Topic: Ceil function
Next Topic: collection pool and global temporary table problem
Goto Forum:
  


Current Time: Sun Dec 04 18:31:36 CST 2016

Total time taken to generate the page: 0.04080 seconds