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: 25
Registered: August 2012
Junior 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: 16998
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.

Report message to a moderator

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: 25
Registered: August 2012
Junior 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

Report message to a moderator

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: 16998
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.

Report message to a moderator

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: 25
Registered: August 2012
Junior Member
Perfect. Thank you for your help, its much appreciated!

Report message to a moderator

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: 9170
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.

Report message to a moderator

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: 25
Registered: August 2012
Junior 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

Report message to a moderator

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

Report message to a moderator

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: 25
Registered: August 2012
Junior 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

Report message to a moderator

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: 20112
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;

Report message to a moderator

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: 25
Registered: August 2012
Junior 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!

Report message to a moderator

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: 20112
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/

Report message to a moderator

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: 9170
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?

Report message to a moderator

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: 25
Registered: August 2012
Junior Member
Thanks for the info. I was just investigating and playing. The trigger is not needed. Thanks for all the info though guys!

Report message to a moderator

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: 989
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.

Report message to a moderator

Previous Topic: Question on insert statement
Next Topic: Updating a duplicate Primarykey
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 26 02:17:07 CDT 2013

Total time taken to generate the page: 0.06748 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.