Home » SQL & PL/SQL » SQL & PL/SQL » Convert date to numeric (Oracle SQL)
Convert date to numeric [message #567912] Mon, 08 October 2012 05:17 Go to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Hi everyone,

I currently have a field which stores a date (date employer started with the company). I want to create a view which stores this as number of days employed (e.g if star date 1st October view will say 8). Does anyone know how to convert a date to a number?

Thanks

Steve

[EDITED by LF: disabled smilies in this message]

[Updated on: Mon, 08 October 2012 05:21] by Moderator

Report message to a moderator

Re: Convert date to numeric [message #567914 is a reply to message #567912] Mon, 08 October 2012 05:22 Go to previous messageGo to next message
Littlefoot
Messages: 19608
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Subtract SYSDATE and the date when that employee was hired. The difference is number of DAYS.
Re: Convert date to numeric [message #567916 is a reply to message #567914] Mon, 08 October 2012 05:33 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Ah yes that looks good. However is there a way to only select the first digit, since at the moment looks like this

7.47890046296296296296296296296296296296

Thanks again
Re: Convert date to numeric [message #567917 is a reply to message #567916] Mon, 08 October 2012 05:34 Go to previous messageGo to next message
Littlefoot
Messages: 19608
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suppose you don't store hours nor minutes in a table, do you? Then, TRUNC(SYSDATE) - HIREDATE could be your first option.
Re: Convert date to numeric [message #567919 is a reply to message #567917] Mon, 08 October 2012 05:45 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Perfect. Thank you for your help, its much appreciated!
Re: Convert date to numeric [message #567926 is a reply to message #567919] Mon, 08 October 2012 06:28 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or you could use the round (or floor or ceil) function to get rid of the decimal.
Re: Convert date to numeric [message #567930 is a reply to message #567926] Mon, 08 October 2012 06:57 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Time for my next question. Is there a way to copy the data from my view (EMPLOYMENT_DETAILS.DAYS_EMPLOYED) to a table(COMPANY_PERSON_ALL.FREE_FIELD4) automatically. Would a trigger do this? Don't know much about creating triggers so wouldnt know!

Thanks
Re: Convert date to numeric [message #567931 is a reply to message #567912] Mon, 08 October 2012 07:06 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Instead of Triggers:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#i1025919


Regards,
Veeresh
Re: Convert date to numeric [message #567960 is a reply to message #567931] Mon, 08 October 2012 09:17 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Thanks for the heads up. Had a read through and created a trigger.

create or replace TRIGGER PCUK_DAYS_EMPLOYED_SS 
INSTEAD OF INSERT ON PCUK_DAYS_EMPLOYED 
   FOR EACH ROW 
BEGIN 
    INSERT INTO COMPANY_PERSON_ALL  
       (FREE_FIELD4)  
    VALUES  
       (:NEW.days_employed)
END PCUK_DAYS_EMPLOYED_SS;


However getting a couple of errors

Error(12): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

Re: Convert date to numeric [message #567961 is a reply to message #567960] Mon, 08 October 2012 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
do like below instead
create or replace TRIGGER PCUK_DAYS_EMPLOYED_SS 
INSTEAD OF INSERT ON PCUK_DAYS_EMPLOYED 
   FOR EACH ROW 
BEGIN 
    INSERT INTO COMPANY_PERSON_ALL  
       (FREE_FIELD4)  
    VALUES  
       (:NEW.days_employed);
END PCUK_DAYS_EMPLOYED_SS;
Re: Convert date to numeric [message #567965 is a reply to message #567961] Mon, 08 October 2012 09:52 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Simple as that! however do not think it is working as expected. If we look at an example record in the view

select DAYS_EMPLOYED from PCUK_DAYS_EMPLOYED WHERE EMP_NO = '4'

Shows 25

However SELECT FREE_FIELD4 FROM COMPANY_PERSON_ALL WHERE EMP_NO = '4'

Shows 4343 and not 25?

Sorry new to triggers!
Re: Convert date to numeric [message #567966 is a reply to message #567965] Mon, 08 October 2012 09:55 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Convert date to numeric [message #567975 is a reply to message #567966] Mon, 08 October 2012 16:58 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
The above trigger will only create new records in COMPANY_PERSON_ALL with free_field4 populated and all other columns null.
It's an instead of trigger, the code it executes happens instead of anything else.
So that insert needs to include more columns and and will be needing an update trigger as well.

However, the requirement makes no sense. Why are you trying to store a value that's constantly changing?
Re: Convert date to numeric [message #568024 is a reply to message #567975] Tue, 09 October 2012 03:31 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Thanks for the info. I was just investigating and playing. The trigger is not needed. Thanks for all the info though guys!

Re: Convert date to numeric [message #568512 is a reply to message #568024] Fri, 12 October 2012 13:14 Go to previous message
Bill B
Messages: 1099
Registered: December 2004
Senior Member
I know you were playing, but as a solid rule never store a value that can be calculated easily from other data.
Previous Topic: Question on insert statement
Next Topic: Updating a duplicate Primarykey
Goto Forum:
  


Current Time: Wed Sep 17 22:58:12 CDT 2014

Total time taken to generate the page: 0.13870 seconds