oracle中的日期列 [英] Date column in oracle

问题描述

我有一个关于 oracle 中日期列的简单问题

I have a simple question on date column in oracle

假设我有一个名为 orders 的表，其中有几个字段，例如 orderid、orderdate、orderno 等.

Suppose I have a table called orders and few fields in it like orderid, orderdate, orderno etc..

我想要像 orderno 这样的信息，没有在特定日期下的订单.为了测试这一点，我在我的测试应用程序上创建了几个订单.我写了下面的查询

I want the info like orderno, no of orders made on a particular day. To test this, I have created few orders on my test application. I have written the below query

SELECT orderid,orderno FROM orders WHERE orderdate='29-SEP-16'; 

我在查询中提到的日期格式是正确的.上述查询不返回任何内容.如果我将 where 条件更改为 >'28-SEP-16' 就可以了.

the date format I mentioned in the query is correct. The above query returns nothing. If I change the where condition to >'28-SEP-16' it works.

为什么我的第一个查询不起作用?

Why my 1st query doesn't work?

推荐答案

date 列没有格式(好吧，date 确实有一个特定的压缩二进制非常非人类可读的表示).Oracle 将尝试使用会话的 nls_date_format 将字符串隐式转换为日期，并将使用会话的 nls_date_format 显示日期的字符串表示(假设您的客户端应用程序没有覆盖这些).但是，您不应依赖隐式数据类型转换.您应该真正使用日期文字或使用 to_date 进行显式转换.

A date column does not have a format (well, a date does have a particular packed binary representation that is very much non human readable). Oracle will attempt to implicitly cast a string to a date using your session's nls_date_format and will use the session's nls_date_format to display a string representation of a date (assuming your client application does not override these). You should not rely on implicit data type conversion, however. You should really use date literals or use to_date to do an explicit cast.

date 列始终包含时间.您的 nls_date_format 可能包含也可能不包含时间组件，因此时间组件可能会或可能不会显示.但它一直都在.

A date column always includes a time. Your nls_date_format may or may not include a time component so the time component may or may not be displayed. But it is always there.

假设你的 nls_date_format 是 dd-mon-rr，查询

Assuming your nls_date_format is dd-mon-rr, the query

SELECT orderid,orderno 
  FROM orders 
 WHERE orderdate='29-SEP-16'; 

将向您显示 orderdate 为 2016 年 9 月 29 日午夜的所有订单.它不会显示时间组件是午夜之后的任何行.您可以通过进行不等式比较来解决这个问题.使用日期文字，那就是

will show you all orders where orderdate is Sept 29, 2016 at midnight. It will not show rows where the time component is anything after midnight. You can fix that by doing an inequality comparison. Using a date literal, that would be

SELECT orderid,orderno 
  FROM orders 
 WHERE orderdate >= date '2016-09-29';

或

SELECT orderid,orderno 
  FROM orders 
 WHERE orderdate >= date '2016-09-29'
   AND orderdate <  date '2016-09-30';

如果你想指定一个范围.或者，您可以截断 orderdate 的时间部分并进行相等比较.我将在此处展示使用 to_date 进行显式转换

if you want to specify a range. Alternately, you could truncate the time portion of orderdate and do an equality comparison. I'll show the use of to_date for explicit conversions here

SELECT orderid,orderno 
  FROM orders 
 WHERE trunc(orderdate) = to_date( '29-SEP-16', 'DD-MON-RR' )

但是，如果您这样做，您可能需要 trunc(orderdate) 上的基于函数的索引.

If you do this, however, you are likely to need a function-based index on trunc(orderdate).

这篇关于oracle中的日期列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！