oracle 时间函数 TRUNC (date)

矫情吗；* 2022-05-10 15:42 438阅读 0赞

![trunc_date.gif][]

TRUNC(date [, fmt ])

[https://docs.oracle.com/cd/B19306\_01/server.102/b14200/functions201.htm][https_docs.oracle.com_cd_B19306_01_server.102_b14200_functions201.htm]

Purpose

The `TRUNC` (date) function returns `date` with the time portion of the day truncated to the unit specified by the format model `fmt`. The value returned is always of datatype `DATE`, even if you specify a different datetime datatype for `date`. If you omit `fmt`, then `date` is truncated to the nearest day. Please refer to ["ROUND and TRUNC Date Functions"][ROUND and TRUNC Date Functions] for the permitted format models to use in `fmt`.

`TRUNC`（date）函数返回*截断到指定单位的date*。返回的值始终为数据类型`DATE`，即使您为其指定了不同的datetime数据类型`date`。如果省略`fmt`，则`date`截断到最近的一天。有关允许使用的格式模型，请参阅[“ROUND和TRUNC日期函数”][ROUND and TRUNC Date Functions]`fmt`。

[https://docs.oracle.com/cd/B19306\_01/server.102/b14200/functions230.htm\#i1002084][ROUND and TRUNC Date Functions]

The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.

默认模型“DD”将日期舍入或截断为具有午夜时间的日期

如

select trunc(sysdate) from dual

![70][]

select to\_char(trunc(sysdate),'YYYY-MM-DD HH:MI:SS') from dual

![70 1][]

<table> 
 <thead> 
  <tr> 
   <th>Format Model</th> 
   <th>Rounding or Truncating Unit</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <pre>CC
SCC
</pre> </td> 
   <td> <p>One greater than the first two digits of a four-digit year</p> <p>意思是&nbsp; 一个比四位数的年份的前两位大一</p> <p>比如 2018 前两位加1 则为 2001&nbsp;</p> </td> 
  </tr> 
 </tbody> 
</table>

select trunc(sysdate,'CC') from dual

![70 2][]

用字符串完整显示上面的时间

select to\_char(trunc(sysdate,'CC'),'YYYY-MM-DD HH24:MI:SS') from dual

![70 3][]

**即CC 格式将日期截取后 年取前两位加一，后面为1月1日的午夜时间 **

select trunc(TO\_DATE('2218-10-18','YYYY-MM-DD'),'CC') from dual

![70 4][]

<table> 
 <tbody> 
  <tr> 
   <td> <pre>SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y
</pre> </td> 
   <td> <p>Year (rounds up on July 1)</p> </td> 
  </tr> 
 </tbody> 
</table>

select to\_char(**trunc(sysdate,'Y')**,'YYYY-MM-DD HH24:MI:SS') from dual

直接将年后的数据置为1月1日的午夜时间

![70 5][]

select to\_char(**trunc(sysdate,'IY'),**'YYYY-MM-DD HH24:MI:SS') from dual

![70 6][] 应该是 符合 ISO标准的时间

<table> 
 <tbody> 
  <tr> 
   <td> <pre>Q
</pre> </td> 
   <td> <p>Quarter (rounds up on the sixteenth day of the second month of the quarter)</p> <p>&nbsp;Quarter 四份之一 季度的意思</p> </td> 
  </tr> 
 </tbody> 
</table>

select to\_char(trunc(sysdate,'Q'),'YYYY-MM-DD HH24:MI:SS') from dual  
select to\_char(trunc(TO\_DATE('2018-10-22','YYYY-MM-DD'),'Q'),'YYYY-MM-DD HH24:MI:SS') from dual

![70 7][]

对于trunc来说这个好像是截取到月 ，月后为当月1日的午夜时间

其实不是，在看一个例子

select to\_char(trunc(TO\_DATE('2018-01-22','YYYY-MM-DD'),'Q'),'YYYY-MM-DD HH24:MI:SS\_\_Q') from dualselect to\_char(trunc(TO\_DATE('2018-03-22','YYYY-MM-DD'),'Q'),'YYYY-MM-DD HH24:MI:SS\_\_Q') from dual

![70 8][]第一季度

select to\_char(trunc(sysdate,'MON'),'YYYY-MM-DD HH24:MI:SS\_\_Q') from dual

![70 9][]第四季度

**所以trunc的真正作用是截取到当月所属季度的开始月份的一月一日的午夜时间**

<table> 
 <tbody> 
  <tr> 
   <td> <pre>MONTH
MON
MM
RM
</pre> </td> 
   <td> <p>Month (rounds up on the sixteenth day)</p> </td> 
  </tr> 
 </tbody> 
</table>

select to\_char(trunc(sysdate,'MON'),'YYYY-MM-DD HH24:MI:SS') from dual  
select to\_char(trunc(TO\_DATE('2018-10-22','YYYY-MM-DD'),'MON'),'YYYY-MM-DD HH24:MI:SS') from dual

![70 10][] 截取到当月1号的午夜时间

<table> 
 <tbody> 
  <tr> 
   <td> <pre>WW
</pre> </td> 
   <td> <p>Same day of the week as the first day of the year</p> <p><span style="color:#f33b45;">这个没理解&nbsp;</span></p> </td> 
  </tr> 
 </tbody> 
</table>

select to\_char(trunc(sysdate,'WW'),'YYYY-MM-DD HH24:MI:SS\_\_WW') from dual

![70 11][]

<table> 
 <tbody> 
  <tr> 
   <td> <pre>IW
</pre> </td> 
   <td> <p>Same day of the week as the first day of the ISO year</p> </td> 
  </tr> 
  <tr> 
   <td> <pre>W
</pre> </td> 
   <td> <p>Same day of the week as the first day of the month</p> </td> 
  </tr> 
 </tbody> 
</table>

这个就是开头测试的 截取到当日的午夜时间，也是fmt的默认格式

<table> 
 <tbody> 
  <tr> 
   <td> <pre>DAY
DY
D
</pre> </td> 
   <td> <p>Starting day of the week</p> </td> 
  </tr> 
 </tbody> 
</table>

select to\_char(trunc(sysdate,'DAY'),'YYYY-MM-DD HH24:MI:SS\_\_DAY') from dual

![70 12][]

截取到这周的开始 即星期日的午夜时间

select to\_char(trunc(sysdate,'HH24'),'YYYY-MM-DD HH24:MI:SS') from dual

![70 13][]

J截取到当前小时的零分灵妙

<table> 
 <tbody> 
  <tr> 
   <td> <pre>MI
</pre> </td> 
   <td> <p>Minute</p> </td> 
  </tr> 
 </tbody> 
</table>

截取到分  ，可见分是能截取到的最小单位

[trunc_date.gif]: /images/20220505/c9536ff870ea449094d9e175afc184b3.png
[https_docs.oracle.com_cd_B19306_01_server.102_b14200_functions201.htm]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm
[ROUND and TRUNC Date Functions]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084
[70]: /images/20220505/eccc9c5ecbf9438abcc51fc1995fe6c1.png
[70 1]: /images/20220505/49fc090d868e492a89fdc4194e2fcf36.png
[70 2]: /images/20220505/f2cee60fa3f3425fb35ff6cd6297017e.png
[70 3]: /images/20220505/46b93e89fb934903918e08f9966da07e.png
[70 4]: /images/20220505/47301adc8e9b470bba3157ce47b47ad5.png
[70 5]: /images/20220505/0d677ec2540a42d6a5b45706c97885ac.png
[70 6]: /images/20220505/37d1fcd2b33243c8b45a70c2b06f0946.png
[70 7]: /images/20220505/702a209d04124e61a773eaae273829d4.png
[70 8]: /images/20220505/90163ce728fd4825b1b8c0f91de267a4.png
[70 9]: /images/20220505/bf74dbc219634406888a7d015ae33938.png
[70 10]: /images/20220505/292fb160feef44ee98cfcfcc43953052.png
[70 11]: /images/20220505/608fe2909d76430ab1c543cab98cf27d.png
[70 12]: /images/20220505/4312aafe8676467eb2016c55087027ba.png
[70 13]: /images/20220505/f9a41cb554ef4ebc9eb36f9b6bc5b235.png