|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
March 3rd, 2005, 08:39 AM
|
|||
|
|||
|
help with TO_CHAR function or similar
ok is there a function that would extract the year from a date?
how does to_char work, and how would i use that to extract the year? ok i have found one: EXTRACT(YEAR FROM DATE '1998-03-07') but is that date written in the normal oracle way or .... as in my case i will use the function in a trigger and instead of the date i will reference a field in the table.... here is a trigger that i created but i get errors! Code:
create or replace trigger calculateYearColumn
After insert on Students
for each row
declare
Y number(4);
begin
select EXTRACT(YEAR FROM DATE 'enrolment_date') into Y from
students;
insert into students (enrolment_year) values (Y);
end;
/
errors: Code:
4/2 PL/SQL: SQL Statement ignored 4/32 PL/SQL: ORA-01841: (full) year must be between -4713 and +9999, a nd not be 0 Last edited by paulh1983 : March 3rd, 2005 at 08:53 AM.
|
|
#2
March 3rd, 2005, 09:22 AM
|
||||
|
||||
|
Quote:
See this This example should also help: Code:
select to_char(to_date('1998-03-07', 'YYYY-MM-DD'), 'YYYY') from dual;
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > help with TO_CHAR function or similar |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
