Problem with Check Constraint

From: <justvrk_at_my-dejanews.com>
Date: Mon, 03 Aug 1998 10:31:41 GMT
Message-ID: <6q43id$j3l$1@nnrp1.dejanews.com>

I have designed a table containing a Date column(LogDateTime) required to store date and time together. I want to ensure that the time values stored in this column are between 09:30 and 16:40 (HH24 format).

For this I tried the following:

alter table logdata
add constraint cons_logtimechk
check(to_date(to_char(LogDateTime,'DD-MON-YYYY HH24:MI') ,'DD-MON-YYYY HH24:MI') >= to_date('09:30','HH24:MI') and to_date(to_char(LogDateTime,'DD-MON-YYYY HH24:MI'), 'DD-MON-YYYY HH24:MI') <= to_date('16:30','HH24:MI')) /

To Which Oracle Server responded with :

ORA-02436: date or system variable wrongly specified in CHECK constraint

My Idea about using to_char and back to_date is that it should check time irrespective of the date. By doing so imply that the date defaults to DB date on both sides of the relational operator.

I am working on Oracle 8 loaded on Solaris 2.6

Can anybody explain me where I am going wrong

Thanks in Advance,
Vinayak.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Aug 03 1998 - 05:31:41 CDT