ORACLE SQL小时范围 [英] ORACLE SQL Range of hours

问题描述

我有问题，我的桌子有3列

i have a problem, i have a table with 3 columns

- date - varchar2   - varchar2 -   
| date | start_hour | end_hour |  

我需要进行验证以确保不重叠小时范围.
例如:

I need to make a validation to not overlap hour ranges.
For example:

| date  | start_hour | end_hour |  
| date1 | 09:00AM    | 09:30AM  |  
| date1 | 10:30AM    | 11:30AM  |  
| date1 | 01:00PM    | 03:00PM  |  

假设3行的日期相同.
我需要的是，这些范围不要重叠
我无法插入这样的范围
start_hour = 08:00 AM和end_hour = 09:20 AM，因为09:00 AM和09:30 AM之间的范围已经存在，因此，新范围与表中存在的范围冲突. 我尝试了很多查询，但没有之间，我插入的end_hour必须小于表中的start_hour. 有人知道怎么做吗?

Assuming the date is the same for the 3 rows.
What i need is, to no overlap these ranges
I cant insert a ranges like this
start_hour = 08:00AM and end_hour = 09:20AM, because, a range between 09:00AM and 09:30AM already exist, so, the new range clashes with the range who exist in the table. I tried so many querys, with not between, the end_hour i insert need to be less than the start_hour from a table. Anyone have an idea how to do it?

推荐答案

当我更改小时格式和新的小时，它运行完美. 这是将来遇到同样问题的人的代码.

i already found the solution to my problem, as a recomendation from a comment in my question, when i changed the format of the hours, and the format of the new hours, it worked perfectly. Here is the code for someone who have this same issue in the future.

DECLARE
  l_count NUMBER(1) := 0;
BEGIN

  SELECT COUNT(*)
  INTO   l_count
  FROM   table
  WHERE  start_hour <= :new_start_hour
  AND    end_hour   >= :new_end_hour
  AND    date = :date
  AND    ROWNUM     = 1;
  dbms_output.put_line(l_count);

END;
/

感谢我们的所有帮助.

这篇关于ORACLE SQL小时范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！