I want to enforce that date-times that are entered fall between 9am and 5pm. How do I enforce this with ORACLE CHECK constraints?
+4
A:
SQL> ed
Wrote file afiedt.buf
1 create table date_check (
2 dt date check( to_number( to_char( dt, 'HH24' ) ) between 9 and 16 )
3* )
SQL> /
Table created.
SQL> ed
Wrote file afiedt.buf
1* insert into date_check values( to_date( '01/01/2008 08:30', 'DD/MM/YYYY HH2
4:MI' ) )
SQL> /
insert into date_check values( to_date( '01/01/2008 08:30', 'DD/MM/YYYY HH24:MI'
) )
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C006170) violated
SQL> ed
Wrote file afiedt.buf
1* insert into date_check values( to_date( '01/01/2008 10:30', 'DD/MM/YYYY HH2
4:MI' ) )
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1* insert into date_check values( to_date( '01/01/2008 17:30', 'DD/MM/YYYY HH2
4:MI' ) )
SQL> /
insert into date_check values( to_date( '01/01/2008 17:30', 'DD/MM/YYYY HH24:MI'
) )
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C006170) violated
Justin Cave
2008-10-15 02:16:53
Thanks, it worked. Minor typo: "between 9 and 16" should read between "9 and 17" to match 9am-5pm.Thanks.
Sajee
2008-10-16 00:03:44
I believe it should be between 9 and 16. That allows times from 9:00:00 AM to 4:59:59 PM. Between 9 and 17 allows times from 9:00:00 AM to 5:59:59 PM. If you need to allow 5:00:00, you could add an an OR to_char(dt, 'HH24:MI:SS') = '17:00:00' to the constraint
Justin Cave
2008-10-16 14:00:24