当前位置：文江博客话题详情

PL/SQL 中的日期范围

发布于 2024-09-18 16:40:22 字数 264 浏览 4 评论 0原文

如果我有一个带有名为 created_date 的日期列（日期字段）的表，其值类似于“9/2/2010 5:25:42 PM”。

我想选择从 start_date 到 end_date 的所有行。但是，end_date 可能为null。在本例中，我想选择 created_date 大于 end_date 的所有行。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

━╋う一瞬間旳綻放 2024-09-25 16:40:22

由于 toDate （可以为空）是一个主变量，它比已经给出的解决方案更容易（在这方面都是错误的，顺便说一句）

select * from mytable
  where created_date between v_fromdate
                         and nvl(v_todate, to_date('31.12.9999','dd.mm.yyyy'));

Since toDate (which can be null) is a host variable, it's easier than the solutions already given (which are all wrong in that regard, btw)

select * from mytable
  where created_date between v_fromdate
                         and nvl(v_todate, to_date('31.12.9999','dd.mm.yyyy'));

回复收藏 0 原文

瑶笙 2024-09-25 16:40:22

选择 *
从表
其中created_date >= '2010-09-02'
并且（created_date 为NULL 或created_date <= '2010-09-03'）

回复收藏 0 原文

往事风中埋 2024-09-25 16:40:22

为什么只使用一个简单的 SQL 查询，就像这样：

select xxx from table_names where created_date is null or (created_date >= to_date("02/09/2010", "dd/mm/yyyy") and created_date <= to_date("03/09/2010", "dd/mm/yyyy"));

编辑

您可以定义一个类似于 ammoQ 定义的查询，即类似这样的查询：

select xxx from table_names where created_date is null or created_date >= start_date and created_date <= nvl(end_date, to_date("31/12/9999", "dd/mm/yyyy"));

但是，当您使用 PL/SQL，您可以检查 end_date 参数的可为空性：

IF end_date IS NULL THEN
    select xxx from table_names where created_date is null or created_date >= start_date;
ELSIF
    select xxx from table_names where created_date is null or created_date >= start_date and created_date <= end_date;
END IF;

请注意，如果 created_date 不为空，您可以删除 created_date is null 条件可为空的列...

Why just use a simple SQL query for that, like this one:

select xxx from table_names where created_date is null or (created_date >= to_date("02/09/2010", "dd/mm/yyyy") and created_date <= to_date("03/09/2010", "dd/mm/yyyy"));

Edit

You can define a query like the one defined by ammoQ, i.e. something like that:

select xxx from table_names where created_date is null or created_date >= start_date and created_date <= nvl(end_date, to_date("31/12/9999", "dd/mm/yyyy"));

However, as you are using PL/SQL, you can check the nullability of end_date parameter:

IF end_date IS NULL THEN
    select xxx from table_names where created_date is null or created_date >= start_date;
ELSIF
    select xxx from table_names where created_date is null or created_date >= start_date and created_date <= end_date;
END IF;

Note that you can remove the created_date is null condition if created_date is not a nullable column...

回复收藏 0 原文

枯寂 2024-09-25 16:40:22

select * from yourtable
where created_date >= @StartDate AND created_date <=ISNULL(@EndDate,created_date)

select * from yourtable
where created_date >= @StartDate AND created_date <=ISNULL(@EndDate,created_date)

回复收藏 0 原文

ˉ厌 2024-09-25 16:40:22

SELECT *
  FROM A_TABLE
  WHERE CREATED_DATE >= &START_DATE AND
        (CREATED_DATE <= &END_DATE OR
         &END_DATE IS NULL)

SELECT *
  FROM A_TABLE
  WHERE CREATED_DATE >= &START_DATE AND
        (CREATED_DATE <= &END_DATE OR
         &END_DATE IS NULL)

回复收藏 0 原文

凡间太子 2024-09-25 16:40:22

如果我从你的问题中得到它
这应该有效：

SELECT *
FROM yourTable
WHERE created_date >= to_date('01.09.2010', 'dd.mm.yyyy')
  AND (end_date  <= to_date('02.09.2010', 'dd.mm.yyyy')
  OR end_date   IS NULL);

If i took it right from your question
this should work:

SELECT *
FROM yourTable
WHERE created_date >= to_date('01.09.2010', 'dd.mm.yyyy')
  AND (end_date  <= to_date('02.09.2010', 'dd.mm.yyyy')
  OR end_date   IS NULL);

回复收藏 0 原文

~没有更多了~