与Oracle sql中的日期相比 [英] Comparing with date in Oracle sql

问题描述

当我查询我的表格时，我有一个列'creation_date'，类型为'date'对于基于'creation_date'的不同记录我得到6条记录：

 从test_table中选择distinct creation_date; 
  

输出：

 code> 06-APR-11 
 06-APR-11 
 28-MAR-11 
 06-APR-11 
 06-APR-11 
 18-MAR-11 
  

在这个输出中，4月6日显示4次，即使我在我的查询。另外，当我试图找出与 creation_date 匹配的所有记录，2011年4月6日我没有得到任何结果。以下是我的查询：

  select * from test_table where creation_date = to_date（'06 -APR-11'，'DD-MON -YY'）; 
  

请帮助我在这两个两个查询中出错。 / p>

解决方案

问题是双重的。首先，日期几乎肯定有时间分量。 to_date（'06 -MAR-11'，'DD-MON-YY'）相当于 2011/03/06 00:00:00 。如果您使用 TRUNC（） 功能，您将能够看到当天的所有内容：

  select * 
 from test_table 
其中，trunc（creation_date）= to_date（'06 -MAR-11'，'DD-MON-YY'）; 
  

我将不使用MON datetime格式模型。正如我在此处中所述，这取决于您的区域和设置。使用数字月格式模型更安全。同样地，一直指定世纪作为一年的一部分。

 其中，trunc（creation_date）= to_date（'06 -03-YY11' ，DD-MM-YYYY）; 
  

您的第二个问题绝对是您的 NLS_DATE_FORMAT ;它似乎没有考虑到时间，因此你为什么看到4个相同的日期。这只是控制数据显示的方式而不是如何存储。

您可以使用以下方式更改：

  ALTER SESSION SET NLS_DATE_FORMAT =DD / MM / YYYY HH24：MI：SS
  pre> 
 
 
如果我使用以下设置测试环境：
  create table test_table（creation_date date）; 
插入test_table值（sysdate）; 
插入到test_table值（sysdate  -  0.01）; 
 alter session set nls_date_format =YYYY / MM / DD; 
  
您可以看到返回的数据不包含时间（尽管SYSDATE）：
  SQL> select * from test_table; 
 
 CREATION_D 
 ---------- 
 2013/04/12 
 2013/04/12 
  
更改NLS_DATE_FORMAT并执行相同的SELECT，您现在可以获得时间部分：
  SQL> alter session set nls_date_format =YYYY / MM / DD HH24：MI：SS; 
 
会话更改。 
 
 SQL> select * from test_table; 
 
 CREATION_DATE 
 ------------------- 
 2013/04/12 12:48:41 
 2013/04/12 12:34:17 
  
最后，当试图单独选择今天的日期没有行将被退回：
  SQL>从test_table 
 3中选择* 
 2其中，creation_date = to_date（'20130412'，'yyyymmdd'）; 
 
没有行选择
  
但是，当使用 TRUNC（）仅在字段的日期部分进行比较，再次获取所有行：
  SQL>从test_table 
 3中选择* 
 2其中，trunc（creation_date）= to_date（'20130412'，'yyyymmdd'）; 
 
 CREATION_DATE 
 ------------------- 
 2013/04/12 12:48:41 
 2013/04/12 12:34:17 
  
要真正回答你的第二个问题，如果你想要唯一日期您可以重新使用 TRUNC（）函数：
  select distinct trunc（creation_date）
 from test_table 
  
 
I have a column 'creation_date' which is of type 'date', when I am querying my table for distinct records based on 'creation_date' I am getting 6 records:
select distinct creation_date from test_table;
output:
06-APR-11
06-APR-11
28-MAR-11
06-APR-11
06-APR-11
18-MAR-11
In this output 6th April is displayed 4 times even when I used distinct in my query. Also when I am trying to find out all records which are matching with creation_date of 6th April 2011 I am not getting any results. Below is my query:
select * from  test_table where creation_date = to_date('06-APR-11','DD-MON-YY');
Please help me where I am doing wrong in these two queries.
解决方案
The problem is twofold. Firstly the dates almost definitely have time-components. to_date('06-MAR-11','DD-MON-YY') is equivalent to 2011/03/06 00:00:00. If you use the TRUNC() function you will be able to see everything for that day:
select * 
  from test_table
 where trunc(creation_date) = to_date('06-MAR-11','DD-MON-YY');
I would not use the MON datetime format model. As I explain here it depends on your region and settings. It's safer to use a numeric month format model instead. Equally, always specify century as part of the year.
where trunc(creation_date) = to_date('06-03-YY11','DD-MM-YYYY');
Your second problem is almost definitely your NLS_DATE_FORMAT; it appears to not take into account the time, hence why you see 4 identical dates. This only governs the manner in which data is displayed not how it is stored.

You can change this using something like:
ALTER SESSION SET NLS_DATE_FORMAT = "DD/MM/YYYY HH24:MI:SS"
If I set up a test environment using the following:
create table test_table ( creation_date date );
insert into test_table values ( sysdate );
insert into test_table values ( sysdate - 0.01 );
alter session set nls_date_format = "YYYY/MM/DD";
You can see the data returned does not include time (though SYSDATE does):
SQL> select * from test_table;

CREATION_D
----------
2013/04/12
2013/04/12
Altering the NLS_DATE_FORMAT and performing the same SELECT, you now get a time component:
SQL> alter session set nls_date_format = "YYYY/MM/DD HH24:MI:SS";

Session altered.

SQL> select * from test_table;

CREATION_DATE
-------------------
2013/04/12 12:48:41
2013/04/12 12:34:17
Lastly, when trying to select today's date alone no rows will be returned:
SQL> select *
  2    from test_table
  3   where creation_date = to_date('20130412','yyyymmdd');

no rows selected
But, when using TRUNC() to compare on only the date portion of the field you get all your rows again:
SQL> select *
  2    from test_table
  3   where trunc(creation_date) = to_date('20130412','yyyymmdd');

CREATION_DATE
-------------------
2013/04/12 12:48:41
2013/04/12 12:34:17
To actually answer your second question, if you want unique dates you can re-use the TRUNC() function:
select distinct trunc(creation_date)
  from test_table


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