Oracle 数据库中的DATE类型 我想获得这列数据的年和月

Question

Oracle 数据库中有列数据类型是DATE 我想获得这列数据的年和月 进行判断是不是今年这个月的数据

Answer 1

select to_char(col,'yyyy') "年",
       to_char(col,'mm') "月"
from tb

Answer 2

-- 查询本月的所有数据：
where date_column >=trunc(sysdate,'mm') 
   and date_column<trunc(add_months(sysdate,1),'mm');

Answer 3

SQL> select sysdate from dual;
/*
SYSDATE
-----------
2011-3-23 1
*/
create table t(
 col_date date);
insert into t(col_date)
values(to_date('2011-01-15','yyyy-mm-dd'));
insert into t(col_date)
values(to_date('2011-02-10','yyyy-mm-dd'));
insert into t(col_date)
values(to_date('2011-03-01','yyyy-mm-dd'));
insert into t(col_date)
values(to_date('2011-03-23','yyyy-mm-dd'));
insert into t(col_date)
values(to_date('2010-01-25','yyyy-mm-dd'));
insert into t(col_date)
values(to_date('2009-12-11','yyyy-mm-dd'));
--
SQL> select * from t;
/*
COL_DATE
-----------
2011-1-15
2011-3-1
2011-3-23
2011-2-10
2010-1-25
2009-12-11
*/
SQL> select * from t
  2  where to_char(col_date,'yyyy')=to_char(sysdate,'yyyy') and
  3        to_char(col_date,'mm')=to_char(sysdate,'mm');
/*
COL_DATE
-----------
2011-3-1
2011-3-23
*/

Answer 4

这样挺好，也可以：
where trunc(data_column,'mm') = trunc(sysdate,'mm')

Answer 5

小弟 我对ORACLE不怎么了解 按前面朋友说的 或得上个月的 这样写对？
WHERE     (CHECKTIME >= TRUNC(SYSDATE, 'mm') - 1) AND (CHECKTIME < TRUNC(ADD_MONTHS(SYSDATE, 1), 'mm') - 1)

Answer 6

-- 请看下面的两个写法的执行效率
-- (有些东东，并不是也可以就行啦，在数据量非常大的表，我们的每个SQL语句都要考虑其效率)hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate-1) and cdate<trunc(sysdate);  COUNT(*)
----------
     82158已用时间:  00: 00: 00.25执行计划
----------------------------------------------------------
Plan hash value: 2668176725---------------------------------------------------------------------------------------------
| Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                        |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                        |     1 |     8 |            |          |
|*  2 |   FILTER           |                        |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| MOBILEFRENDS_CDATE_IDX |    48 |   384 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter(TRUNC(SYSDATE@!-1)<TRUNC(SYSDATE@!))
   3 - access("CDATE">=TRUNC(SYSDATE@!-1) AND "CDATE"<TRUNC(SYSDATE@!))
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        221  consistent gets
          0  physical reads
          0  redo size
        345  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedhll@SZTYORA> select count(*) from mobilefrends where to_char(cdate,'yyyymm')=to_char(sysdate,'yyyymm');  COUNT(*)
----------
   1594928已用时间:  00: 00: 12.42执行计划
----------------------------------------------------------
Plan hash value: 3731074549------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     1 |     8 | 11450  (14)| 00:02:18 |
|   1 |  SORT AGGREGATE       |                        |     1 |     8 |            |          |
|*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 11450  (14)| 00:02:18 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter(TO_CHAR(INTERNAL_FUNCTION("CDATE"),'yyyymm')=TO_CHAR(SYSDATE@!,'yyyymm'))
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      36816  consistent gets
      36549  physical reads
          0  redo size
        346  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Answer 7

-- 若上面的语句，大家还不可信的话，请看下面的两个SQL语句：
hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate,'mm') and cdate<trunc(add_months(sysdate,1),'mm');  COUNT(*)
----------
   1595459已用时间:  00: 00: 00.51执行计划
----------------------------------------------------------
Plan hash value: 2668176725---------------------------------------------------------------------------------------------
| Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                        |     1 |     8 |   994   (1)| 00:00:12 |
|   1 |  SORT AGGREGATE    |                        |     1 |     8 |            |          |
|*  2 |   FILTER           |                        |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| MOBILEFRENDS_CDATE_IDX |   347K|  2712K|   994   (1)| 00:00:12 |
---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter(TRUNC(SYSDATE@!,'fmmm')<TRUNC(ADD_MONTHS(SYSDATE@!,1),'fmmm'))
   3 - access("CDATE">=TRUNC(SYSDATE@!,'fmmm') AND
              "CDATE"<TRUNC(ADD_MONTHS(SYSDATE@!,1),'fmmm'))
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4234  consistent gets
          0  physical reads
          0  redo size
        346  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedhll@SZTYORA> select count(*) from mobilefrends where to_char(cdate,'yyyymm')=to_char(sysdate,'yyyymm');  COUNT(*)
----------
   1595459已用时间:  00: 00: 09.67执行计划
----------------------------------------------------------
Plan hash value: 3731074549------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     1 |     8 | 11450  (14)| 00:02:18 |
|   1 |  SORT AGGREGATE       |                        |     1 |     8 |            |          |
|*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 11450  (14)| 00:02:18 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter(TO_CHAR(INTERNAL_FUNCTION("CDATE"),'yyyymm')=TO_CHAR(SYSDATE@!,'yyyymm'))
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      36816  consistent gets
          0  physical reads
          0  redo size
        346  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Answer 8

-- 从10楼的两个SQL的执行计划，可以看出：
-- 第一个查询是走的“索引范围扫描”，执行时间为0.51秒，逻辑读4234次；
-- 而第二个查询是走的“全索引扫描”，执行时间为9.67秒，逻辑读36816次；-- 我想请问大家：到底哪个查询好呢？

Answer 9

--下面语句获得的是当月的所有数据
select * from table where  date between TO_DATE('2011-03-01','YYYY-MM-DD')
AND LAST_DAY(TO_DATE('2011-03-01','YYYY-MM-DD'))

Answer 10

where date_column>=add_months(trunc(sysdate,'mm'),-1)
    and date_column < trunc(sysdate,'mm');

Answer 11

-- date_colum字段大于等于上个月的第一天的0点，小于本月第一天的0点的所有数据，
-- 不就是上个月所有的数据吗？music@SZTYORA> select add_months(trunc(sysdate,'mm'),-1)
  2  from dual;ADD_MONTHS(TRUNC(SY
-------------------
2011-02-01 00:00:00music@SZTYORA> select trunc(sysdate,'mm') from dual;TRUNC(SYSDATE,'MM')
-------------------
2011-03-01 00:00:00

Answer 12

-- 方法一：用 last_day()函数
hll@SZTYORA> select count(*) from mobilefrends
  2   where cdate between TO_DATE('2011-02-01','YYYY-MM-DD')
  3     and last_day(TO_DATE('2011-02-01','YYYY-MM-DD'));  COUNT(*)
----------
   1807266已用时间:  00: 00: 00.67-- 方法二：
hll@SZTYORA> select count(*) from mobilefrends
  2   where cdate between add_months(trunc(sysdate,'mm'),-1)
  3     and trunc(sysdate,'mm');  COUNT(*)
----------
   1810926已用时间:  00: 00: 00.75-- 方法三：
hll@SZTYORA> select count(*) from mobilefrends
  2   where cdate>=add_months(trunc(sysdate,'mm'),-1)
  3     and cdate<trunc(sysdate,'mm');  COUNT(*)
----------
   1810926已用时间:  00: 00: 00.75
hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate,'mm')-1 and cdate<trunc(sysdate,'mm');  COUNT(*)
----------
      3660已用时间:  00: 00: 00.09-- 下面查询得到的结果正好和方法一相同：
hll@SZTYORA> select 1810926-3660 from dual;1810926-3660
------------
     1807266已用时间:  00: 00: 00.10
-- 由上面可以看到，用 last_day()函数，只到2月份最后一天0点的数据，而2月份最后一天0点后的数据不包括在内，所以，方法一：用 last_day()函数是不正确的！