1

Here's the code needed.

I've edited the content.

CREATE OR REPLACE TRIGGER horaire_heure
BEFORE INSERT OR UPDATE ON Horaire
FOR EACH ROW
BEGIN
:NEW.heure_debut := to_char(:NEW.heure_debut,'hh24:mi:ss');
:NEW.heure_fin := to_char(:NEW.heure_fin,'hh24:mi:ss');
END;
/

And the input

INSERT INTO Horaire
VALUES (1,2,TO_DATE('02/01/2000 13:30:00','DD/MM/YYYY HH24:MI:SS'),TO_DATE('02/01/2000 16:30:00','DD/MM/YYYY HH24:MI:SS'),NULL);

Now it pop ups ORA-01843. Telling me the month is not valid.

What's wrong?

Greiko
  • 17
  • 4
  • 2
    What is the data type of `heure_debut` and `heure_fin`? Are they dates? What is it that you expect this trigger to accomplish? – Justin Cave Nov 15 '14 at 05:06
  • 1
    The `to_char(to_date())` calls are useless and show a lack of understanding how dates work in Oracle. You are providing `date` values in the insert statement. So the trigger first implicitly converts the provided `date` (from `:new.heure_debut`) to a `varchar` value - because `to_date()` expects a `varchar`. Then `to_date()` converts that back to a `date` which is then immediately converted to a `varchar` because of the `to_char()` call. That `varchar` value is then *again* converted back to a `date` value because of the assignment to a `date` column. What on earth are you trying to do there? –  Nov 15 '14 at 10:38
  • The column is DATE but the default DATE is DD/MM/YYYY and i can't change that because of restricted things. I need to convert the format of this column to HH24:MI:SS – Greiko Nov 16 '14 at 04:20
  • A `DATE` column does not have a format. Therefore, you cannot change the format. Data in a `DATE` column is always stored in a non human-readable format that has absolutely nothing to do with how the data is displayed. The conversion of a `DATE` to a human-readable string for display is something that the client application is responsible for and often depends on the client's NLS settings. If you want the data to display a particular way, you'd probably want to use a `to_char` function in your `SELECT` statement or to modify your client settings. – Justin Cave Nov 16 '14 at 06:45

1 Answers1

0

In your insert statement you convert '2000/01/02 13:30:00' to date format and in the trigger you use to_Date to convert a date value! and after that get the time part from the '2000/01/02 13:30:00' and because of 2 date converts your time will be '00:00:00'!

In another hand, i think type of heure_debut field is DATE. and you want to change it to varchar in trigger.

I think you have to change the place of to_char and to_date to insert time instead of date and time.

Ramin Mirahmadi
  • 141
  • 5
  • The column is DATE but the default DATE is DD/MM/YYYY and i can't change that because of restricted things. I need to convert the format of this column to HH24:MI:SS – Greiko Nov 16 '14 at 04:21
  • If you write another to_date in trigger , it has to be resolved `CREATE OR REPLACE TRIGGER horaire_heure BEFORE INSERT OR UPDATE ON Horaire FOR EACH ROW BEGIN :NEW.heure_debut := to_date(to_char(:NEW.heure_debut,'hh24:mi:ss'),'hh24:mi:ss'); :NEW.heure_fin := to_date(to_char(:NEW.heure_fin,'hh24:mi:ss'),'hh24:mi:ss'); END; /` but it is stored like 2014-11-01 1:30:00 PM in your field. i want to say if you want to store just time value you have 2 options : 1- ignore date value 2- store time in a number field see this [link](http://stackoverflow.com/questions/12215281/oracle-insert-only-time) – Ramin Mirahmadi Nov 16 '14 at 08:02
  • Thanks, I ignored date value! – Greiko Nov 17 '14 at 20:24