SQL BETWEEN 不起作用

发布于 2024-11-07 11:34:07 字数 394 浏览 1 评论 0原文

我在 Oracle 数据库上运行以下语句。

SELECT br.Number
  FROM Billing_History br 
 WHERE TRUNC(br.History_Date) BETWEEN to_date('01-Jan-99', 'DD-Mon-YY HH:MI:SS') 
                                  AND to_date('11-May-99', 'DD-Mon-YY HH:MI:SS')

该表中肯定有属于这些日期之间的记录。他们都有一个与之配套的数字，但由于某种原因，这没有返回任何数字。它什么也没返回。

数据库中的日期格式为“01-Jan-11”。所以看来我也以正确的格式输入日期。你发现我写的 SQL 有什么问题吗？

原文

I have the following statement being run on an oracle database.

SELECT br.Number
  FROM Billing_History br 
 WHERE TRUNC(br.History_Date) BETWEEN to_date('01-Jan-99', 'DD-Mon-YY HH:MI:SS') 
                                  AND to_date('11-May-99', 'DD-Mon-YY HH:MI:SS')

There are definitely records in that table that fall between those dates. And they all have a Number that goes with them, but for some reason this isn't returning any Numbers. It's returning nothing at all.

The dates in the database are in this format '01-Jan-11'. So it seems like I'm putting the dates in the correct format too. Do you see anything wrong with the SQL I wrote?

分享到QQ

分享到微博

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

发布评论

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

千年*琉璃梦 2024-11-14 11:34:07

问题不在于格式模型的时间组件，而是“YY”组件，这意味着您的年份转换为 2099，而不是 1999。尝试说明：

SQL> SELECT to_char(to_date('01-Apr-99','DD-Mon-YY'),'DD-Mon-YYYY') thedate
       FROM dual;

THEDATE
-----------
01-Apr-2099

SQL>

使用 RR 或 YYYY 作为年份的格式模型组件使用 20 世纪日期时。

编辑：

您声明“数据库中的日期采用这种格式‘01-Jan-11’”。这是 Oracle 中对日期的常见但不正确的解释。 DATE 字段始终以相同的内部格式存储< /a>.这完全取决于如何在转换函数中使用格式模型，该函数指示如何将数据转换为内部格式或从内部格式转换为数据。

The problem is not the time component of the format model, it's the 'YY' component, which would mean in your year is converted to 2099, not 1999. Try this to illustrate:

SQL> SELECT to_char(to_date('01-Apr-99','DD-Mon-YY'),'DD-Mon-YYYY') thedate
       FROM dual;

THEDATE
-----------
01-Apr-2099

SQL>

Either use RR or YYYY as a format model component for year when using 20th century dates.

Edit:

You make the statement "The dates in the database are in this format '01-Jan-11'." This is a common, but incorrect, interpretation of dates in Oracle. DATE fields are always stored in the same internal format. It's all about how you use the format model in conversion functions that dictates how the data is converted to/from internal format.

回复收藏 0 原文

是伱的 2024-11-14 11:34:07

在日期格式中使用 RR 而不是 YY。它可能会将这些日期提取为 2099 年而不是 1999 年。

SELECT br.Number FROM Billing_History br WHERE  
TRUNC(br.History_Date) BETWEEN to_date('01-Jan-99', 'DD-Mon-RR HH:MI:SS') 
AND to_date('11-May-99', 'DD-Mon-RR HH:MI:SS')

Use RR in your date format instead of YY. It is probably picking up those dates as 2099 instead of 1999.

SELECT br.Number FROM Billing_History br WHERE  
TRUNC(br.History_Date) BETWEEN to_date('01-Jan-99', 'DD-Mon-RR HH:MI:SS') 
AND to_date('11-May-99', 'DD-Mon-RR HH:MI:SS')

回复收藏 0 原文