ORACLE 中如何将日期中年月日截取到年月

1、创建测试表，create table test_date(v_date date)

2、插入测试数据，

insert into test_date

select sysdate - level * 10 from dual connect by level <100

3、查询表中所有记录，select * from test_date，可以看到时间格式为年月日时分秒，

4、编写sql，将时间截取到年月，

select t.*,

     to_char(v_date, 'yyyymm') v_date_mon1,

     trunc(v_date, 'mm') v_date_mon2

from TEST_DATE t

Trunc Function (with dates)

In Oracle/PLSQL, the trunc function returns a date truncated to a specific unit of measure.

oracle中，trunc函数返回一个按照特定计量单位截取后的date值

The syntax for the trunc function is:

语法如下：

trunc ( dat1e, [ format ] )

da1te is the date to truncate.

da1te是要截断的date

format is the unit of measure to apply for truncating. If the format parameter is omitted, the trunc function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.

format 是截取时依据的计量单位(类似于数字中的精度)。如果format省略，date1就返回当天的日期值,即只保留日期，时间为 0:00:00

Below are the valid format parameters:

以下是合法的参数值：

Unit Valid format parameters

Year 】 SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y

ISO Year】 IYYY, IY, I

Quarter】 Q

Month】 MONTH, MON, MM, RM

Week】 WW

IW】 IW

W】 W

Day】 DDD, DD, J

Start day of the week】 DAY, DY, D

Hour】 HH, HH12, HH24

Minute】 MI

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

trunc(to_date('22-AUG-03'), 'YEAR') would return '01-JAN-03'

trunc(to_date('22-AUG-03'), 'Q') would return '01-JUL-03'

trunc(to_date('22-AUG-03'), 'MONTH') would return '01-AUG-03'

trunc(to_date('22-AUG-03'), 'DDD') would return '22-AUG-03'

trunc(to_date('22-AUG-03'), 'DAY') would return '17-AUG-03'

---