CODEWITHSUNDEEP
sqloracle-databasedate
LaurentDumont
LaurentDumont

Reputation: 31

Oracle 10 SQL - How to use a "00:00" time format in a constraint

Small entry level question with Oracle 10 SQL. I'm creating a table with a column with a "date" type which is supposed to hold values looking like this : "00:00". I have a constraint with checks the time to be between 00:00 and 23:00.

Now, what I can't quite grasp is how to approach the problem. I do feel like I'm missing something quite basic but I can't quite figure out what...

Do I :

1) Extract and check the date inside my constraint? If so, is there a way to do that? Can I insert data looking like this : TO_DATE('13-AUG-66 12:56','DD-MON-YY HH:MI'), and use some kind of "Extract" function inside my constraint?

2) The exercise in question does mention the date type for that particular column. By default, I assume that it doesn't hold hours and needs to be modified using alter_session?

Upvotes: 1

Views: 845

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95062

A date type always has a date part and a time part. It is just a value and has thus no formatting. If you display a time as 22:50 or 10:50pm for example is up to you. You either rely on your settings with to_char(mydate) or specify a format to_char(mydate,'hh24:mi').

This said, you can simply use the time part of your column and ignore the date part. If you want to avoid confusion about different dates being stored, you can use a trigger setting the date part to 01.01.0001 for instance:

create or replace Trigger trg_datetable_datepart
before insert or update of mydate on datetable
for each row
begin
  :new.mydate := to_date( '01.01.0001 ' || to_char(:new.mydate, 'hh24:mi') , 'dd.mm.yyyy hh24:mi' );
end;

To avoid inserts of times after 23h you would write a check constraint:

alter table datetable add constraint check_datetable_timepart check ( to_char(mydate, 'hh24:mi') <= '23:00' );

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231781

A constraint only enforces a restriction. It cannot modify data. A BEFORE INSERT trigger can modify data but is generally less efficient than a constraint.

If you want to create a constraint that ensures that the time component is always midnight

CREATE TABLE table_name (
  col DATE CHECK( col1 = TRUNC( col ))
);

If you want to create a trigger that modifies the data

CREATE OR REPLACE TRIGGER trg_trunc_dt
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  :new.date_column := TRUNC( :new.date_column );
END;

A DATE always contains a day and a time component. Your client may or may not display either component. Many clients will use implicit data type conversion in which case the session's NLS_DATE_FORMAT controls how a DATE is converted to a VARCHAR2 and what elements are incorporated into the string.

Upvotes: 2

Related Questions