Oracle时间函数 to_char()及常用时间格式

冷不防 2022-05-10 14:08 449阅读 0赞

![to_char_date.gif][]

TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])

Oracle  TO\_CHAR()函数返回以指定格式表示的字符串DATE或INTERVAL值。

\{ datetime | interval \}是一个DateTime或一个INTERVAL 类型，是要转换的日期

fmt是一个字符串，用于确定日期转换的格式。  
    如果省略fmt，则date转换VARCHAR2为如下值：  
      DATE 值将转换为默认日期格式的值。  
      TIMESTAMP和TIMESTAMP WITH LOCAL TIME ZONE值将转换为默认时间戳格式的值。  
      TIMESTAMP WITH TIME ZONE 值将使用时区格式转换为默认时间戳中的值。  
        
'nlsparam'参数指定了月和日名称的缩写语言。这个参数可以有这样的形式：  
'NLS\_DATE\_LANGUAGE = 语言 '  
例如，Monday，Mon，January，Jan等。  
如果省略'nlsparam'，则此函数使用会话的默认日期语言。

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

字符串' 17:45:29' 的日期时间格式模型是' HH24:MI:SS'。  
字符串' 11-Nov-1999' 的日期时间格式模型是' DD-Mon-YYYY'。

测试

![70][]

注意 要想达到    ”字符串' 11-Nov-1999' 的日期时间格式模型是' DD-Mon-YYYY' ”。需要指定 'nlsparam' 参数

select TO\_CHAR(sysdate,'DD-Mon-YYYY','NLS\_DATE\_LANGUAGE=English') from dual

![70 1][]

[https://docs.oracle.com/cd/B19306\_01/server.102/b14200/sql\_elements004.htm\#i34510][https_docs.oracle.com_cd_B19306_01_server.102_b14200_sql_elements004.htm_i34510]

[https://docs.oracle.com/cd/B19306\_01/server.102/b14200/sql\_elements004.htm\#i34924][https_docs.oracle.com_cd_B19306_01_server.102_b14200_sql_elements004.htm_i34924]

Uppercase Letters in Date Format Elements

Capitalization in a spelled-out word, abbreviation, or Roman numeral follows capitalization in the corresponding format element. For example, the date format model 'DAY' produces capitalized words like 'MONDAY'; 'Day' produces 'Monday'; and 'day' produces 'monday'.

拼写单词，缩写或罗马数字中的大写在相应的格式元素中大写。例如，日期格式模型'DAY'产生像'MONDAY'这样的大写单词; 'Day'产生'Monday'; 而''day'产生'monday'。

select TO\_CHAR(sysdate,'DAY-Mon-YYYY','NLS\_DATE\_LANGUAGE=English') from dual  
select TO\_CHAR(sysdate,'Day-Mon-YYYY','NLS\_DATE\_LANGUAGE=English') from dual  
select TO\_CHAR(sysdate,'day-Mon-YYYY','NLS\_DATE\_LANGUAGE=English') from dual

因为是英文文档，所以要指定'NLS\_DATE\_LANGUAGE=English'

You can include these characters in a date format model:

*  Punctuation such as hyphens, slashes, commas, periods, and colons
 *  Character literals, enclosed in double quotation marks

These characters appear in the return value in the same location as they appear in the format model.

您可以在日期格式模型中包含这些字符：

*  标点符号，如连字符，斜杠，逗号，句点和冒号
 *  字符文字，用双引号括起来

这些字符出现在返回值中与格式模型中显示的位置相同的位置。

<table> 
 <thead> 
  <tr> 
   <th>Element</th> 
   <th>Specify in TO_* datetime functions?</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <pre><code>-
/
,
.
;
:
"text"
</code></pre> </td> 
   <td> <p><span style="color:#f33b45;">Yes</span></p> </td> 
   <td> <p><span style="color:#f33b45;">Punctuation and quoted text is reproduced in the result.</span></p> <p><span style="color:#f33b45;">格式符中可以包含左边的元素&nbsp;</span></p> </td> 
  </tr> 
 </tbody> 
</table>

select TO\_CHAR(sysdate,' "今天" day-Mon-YYYY') from dual

![70 2][]

其次 格式字符串中还可以包含 空格

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>AD
A.D.
</code></pre> </td> 
   <td> <p>Yes</p> </td> 
   <td> <p>AD indicator with or without periods.</p> <p>AD 在汉语中显示为公元</p> </td> 
  </tr> 
 </tbody> 
</table>

select TO\_CHAR(sysdate,'AD day-Mon-YYYY') from dual

![70 3][]

select TO\_CHAR(sysdate,'A.D. day-Mon-YYYY') from dual   还是一样

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>AM
A.M.
</code></pre> </td> 
   <td> <p>Yes</p> </td> 
   <td> <p>Meridian indicator with or without periods.</p> <p>可以显示当前是下午还是上午&nbsp;</p> </td> 
  </tr> 
 </tbody> 
</table>

select TO\_CHAR(sysdate,'AM day-Mon-YYYY') from dual

![70 4][]

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>BC
B.C.
</code></pre> </td> 
   <td> <p>Yes</p> </td> 
   <td> <p>BC indicator with or without periods.</p> </td> 
  </tr> 
 </tbody> 
</table>

select TO\_CHAR(sysdate,'BC day-Mon-YYYY') from dual

![70 5][]  可见他和AM一样，都表示公元

select TO\_CHAR(sysdate,'D') from dual

![70 6][] 在我们底盘今天是星期四

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>DAY
</code></pre> </td> 
   <td> <p><span style="color:#f33b45;">Yes</span></p> </td> 
   <td> <p><span style="color:#f33b45;">Name of day, padded with blanks to display width of the widest name of day in the date language used for this element.</span></p> </td> 
  </tr> 
 </tbody> 
</table>

select TO\_CHAR(sysdate,'DAY') from dual

![70 7][]

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>DD
</code></pre> </td> 
   <td> <p><span style="color:#f33b45;">Yes</span></p> </td> 
   <td> <p><span style="color:#f33b45;">Day of month (1-31).</span></p> </td> 
  </tr> 
 </tbody> 
</table>

select TO\_CHAR(sysdate,'DD') from dual

![70 8][] 嗯，今天是 10月18号

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>DDD
</code></pre> </td> 
   <td> <p>Yes</p> </td> 
   <td> <p>Day of year (1-366).</p> <p>一年的第多少天</p> </td> 
  </tr> 
 </tbody> 
</table>

select TO\_CHAR(sysdate,'DDD') from dual

![70 9][]

下面这很好理解

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>MI
</code></pre> </td> 
   <td> <p><span style="color:#f33b45;">Yes</span></p> </td> 
   <td> <p><span style="color:#f33b45;">Minute (0-59). 电脑上当前的分钟 比 oracle查出来的分钟 多了3分钟</span></p> </td> 
  </tr> 
  <tr> 
   <td> <pre><code>MM
</code></pre> </td> 
   <td> <p><span style="color:#f33b45;">Yes</span></p> </td> 
   <td> <p><span style="color:#f33b45;">Month (01-12; January = 01).</span></p> </td> 
  </tr> 
  <tr> 
   <td> <pre><code>MON
</code></pre> </td> 
   <td> <p>Yes</p> </td> 
   <td> <p>Abbreviated name of month.</p> </td> 
  </tr> 
  <tr> 
   <td> <pre><code>MONTH
</code></pre> </td> 
   <td> <p>Yes</p> </td> 
   <td> <p>Name of month, padded with blanks to display width of the widest name of month in the date language used for this element.</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>SS
</code></pre> </td> 
   <td> <p><span style="color:#f33b45;">Yes</span></p> </td> 
   <td> <p><span style="color:#f33b45;">Second (0-59).</span></p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <pre>IW
</pre> </td> 
   <td> <p>No</p> </td> 
   <td> <p>Week of year (1-52 or 1-53) based on the ISO standard. 一年中的第几周</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <pre>WW
</pre> </td> 
   <td> <p>No</p> </td> 
   <td> <p>Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.</p> <p>一年中的第几周 ，一年的第一天就是第一周的第一天&nbsp; 即每年的1月1日作为一周的第一天</p> </td> 
  </tr> 
  <tr> 
   <td> <pre>W
</pre> </td> 
   <td> <p>No</p> </td> 
   <td> <p>Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.</p> <p>一个月的第几周， 将每月的第一天作为一周的开始 ，</p> </td> 
  </tr> 
 </tbody> 
</table>

select TO\_CHAR(sysdate,'IW') from dual  --42 一年中的第几周  
select TO\_CHAR(sysdate,'WW') from dual  --42  
select TO\_CHAR(TO\_DATE('2018-01-01','YYYY-MM-DD'),'YYYY-MM-DD DAY') from dual  --2018-10-01 星期一 可见一周第一天就是星期一  即1月1日都是星期一

select TO\_CHAR(sysdate,'W','NLS\_DATE\_LANGUAGE=English')  from dual  --3  一个月中的第几周  
SELECT TO\_DATE('2018-10-01','YYYY-MM-DD') FROM DUAL  --01-10月-18  
select TO\_CHAR(TO\_DATE('2018-10-01','YYYY-MM-DD'),'YYYY-MM-DD DAY') from dual  --2018-10-01 星期一 可见一周第一天就是星期一  即每月1日都是星期一

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>Y,YYY
</code></pre> </td> 
   <td> <p>Yes</p> </td> 
   <td> <p>Year with comma in this position.</p> </td> 
  </tr> 
 </tbody> 
</table>

![70 10][]

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>YYYY
SYYYY
</code></pre> </td> 
   <td> <p><span style="color:#f33b45;">Yes</span></p> </td> 
   <td> <p><span style="color:#f33b45;">4-digit year;&nbsp;<code>S</code>&nbsp;prefixes BC dates with a minus sign.</span></p> </td> 
  </tr> 
  <tr> 
   <td> <pre><code>YYY
YY
Y
</code></pre> </td> 
   <td> <p>Yes</p> </td> 
   <td> <p>Last 3, 2, or 1 digit(s) of year.</p> </td> 
  </tr> 
 </tbody> 
</table>

![70 11][]

[to_char_date.gif]: /images/20220505/a811fedc23994523b64a0215f1498efd.png
[https_docs.oracle.com_cd_B19306_01_server.102_b14200_functions180.htm]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm
[70]: /images/20220505/03cc437ee99c47edbea40e6c8fa098b1.png
[70 1]: /images/20220505/137935149c2c4388a3bb566eefc67b38.png
[https_docs.oracle.com_cd_B19306_01_server.102_b14200_sql_elements004.htm_i34510]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34510
[https_docs.oracle.com_cd_B19306_01_server.102_b14200_sql_elements004.htm_i34924]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
[70 2]: /images/20220505/4e538ad4a7fc46eb90ecf8b00c2e3d88.png
[70 3]: /images/20220505/4f2ee3661f2444afb208280b7058f03b.png
[70 4]: /images/20220505/28abf0a4c8f343b4a8a6ea5f41ea9057.png
[70 5]: /images/20220505/af89e2e256bf4861a807321e7e28c947.png
[70 6]: /images/20220505/53006d855a564f93b59f7b9549520318.png
[70 7]: /images/20220505/8780649409e7446aa9bfd63ecd3ef751.png
[70 8]: /images/20220505/36d7380c84874b4294e379da1bfffe7e.png
[70 9]: /images/20220505/cc5589410c8945cf88416e61701b0a18.png
[70 10]: /images/20220505/4c1eb4ddd4234008ba14685b1d76f7ef.png
[70 11]: /images/20220505/60d0c268cc924e57bddc0aa5c10755c9.png