CODEWITHSUNDEEP
oracle-databasedateconstraints
Beefstu
Beefstu

Reputation: 857

Oracle CONSTRAINT to ensure seconds are zero

Is there a way to create a CONSTRAINT on a date column that will check if the seconds is a value other then 0

I only want to allow values in this format MMDDYYYY HH24:MI

I know the following will be a violation if hours, minutes or seconds are values other than zero.

CONSTRAINT dt_chk check (dt=trunc(dt,'dd'))

Upvotes: 0

Views: 202

Answers (2)

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

Another way to do this might be using a constraint this way:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss' ;

Session altered.

SQL> create table test.test_chk ( c1 date );

Table created.

SQL> alter table test.test_chk add constraint check_second check ( extract(second from cast(c1 as timestamp)) = 0 );

Table altered.

SQL>  insert into test.test_chk values ( '01.01.2020 13:15:22' ) ;
 insert into test.test_chk values ( '01.01.2020 13:15:22' )
*
ERROR at line 1:
ORA-02290: check constraint (TEST.CHECK_SECOND) violated


SQL> insert into test.test_chk values ( '01.01.2020 13:15:00' ) ;

1 row created.

SQL> commit;

Commit complete.

SQL>

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191435

Just truncate to minutes instead of days:

CONSTRAINT dt_chk check (dt=trunc(dt,'MI'))

db<>fiddle

Read more in the documentation.

Upvotes: 5

Related Questions