CODEWITHSUNDEEP
sqloracle-database
Coder
Coder

Reputation: 161

In oracle, how do I create a check constraint that checks if the time is between 10:00:00 and 21:00:00?

In an Oracle database, I have a registration date column. Using SQL, I want to add a check constraint that checks the time of the date is between 10:00:00 and 21:00:00. How can I do this?

Upvotes: 0

Views: 581

Answers (3)

user5683823
user5683823

Reputation:

Ideally, the constraint should use date-time (and interval) expressions and functions as much as possible. Alas, in Oracle date arithmetic works mostly with numbers (representing "days"), so you do need to convert somewhere.

I would do it like this - easiest (in my opinion) for future developers to read and understand:

check (numtodsinterval(reg_date - trunc(reg_date), 'day') 
           between interval '10' hour and interval '21' hour)

trunc(reg_date) returns the date, with the time-of-day truncated to midnight. Then reg_date - trunc(reg_date) returns the time-of-day stored in reg_date - but the result is a number (expressed in days). I convert this to an interval with the numtodsinterval, specifying that the number represents "days". The rest should be obvious.

By the way, if you must check that the time-of-day is, for example, between 10:20 and 21:20, you can use the same approach - use interval 10:20 hour to minute, for example, in the between condition. The advantage (in readability) of writing the condition in this way becomes even clearer in those cases.

Upvotes: 1

Popeye
Popeye

Reputation: 35920

You can use the check constraint as follows:

(Your_date - trunc(your_date))*24 between 10 and 21

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can use:

check (to_char(datecol, 'HH24:MI') between '10:00' and '21:00'))

Note: This includes '21:00'. Use >= and < if you don't want it included.

Upvotes: 0

Related Questions