南大通用GBase 8a将日期转字符串to_char函数功能介绍和样例

发表于 作者 laozizhu

GBase 8a提供了to_char函数，按照指定格式，将日期时间转化为字符串。本文介绍该函数的语法和使用样例。

语法

TO_CHAR(datetime,[FORMAT])

说明

将参数 datetime 转换为字符串，并进行格式化输出。

参数

如下是支持的参数列表，基本兼容Oracle的大部分参数。

模板描述
HH一天的小时数 (01-12)
HH12一天的小时数 (01-12)
HH24一天的小时数 (00-23)
MI分钟 (00-59)
SS秒 (00-59)
SSSSS午夜后的秒 (0-86399)
AM or A.M. or PM or P.M.正午标识，都是大写
Y,YYY带逗号的年（4 和更多位）
YYYY、RRRR年（4和更多位）
YYY年的后三位
YY、RR年的后两位
Y年的最后一位
BC or B.C. or AD or A.D.年标识，都是大写
MONTH、Month、month全长首字母大写月份名
MON、Mon、mon首字母大写缩写月份名（3字符）
MM月份 (01-12)
DAY、Day、day全长首字母大写的星期几
DY、Dy、dy缩写首字母大写星期几（3字符）
DDD一年里的日子(001-366)
DD一个月里的日子(01-31)
D一周里的日子(1-7；SUN=1)
W一个月里的周数
IW、WWISO中的一年的第几周（1-52/53)
CC世纪（2 位）
Q季度
RM罗马数字的月份（I-XII；I=JAN）－大写
rm罗马数字的月份（I-XII；I=JAN）－小写
FF[n]毫秒，默认是6位精度，1<=n<=9
FM删除开头和结尾的空格

用例

年：Y、YY（RR)、YYY、YYYY(RRRR)

对应1-4位的年份输出

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'Y') d;
+------+
| d    |
+------+
| 1    |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'YY') d;
+------+
| d    |
+------+
| 21   |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'YYY') d;
+------+
| d    |
+------+
| 021  |
+------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'YYYY') d;
+------+
| d    |
+------+
| 2021 |
+------+
1 row in set (Elapsed: 00:00:00.00)

RR和RRRR的写法

gbase> select to_char(to_date('2021-02-04 11:15:16.987654321','YYYY-MM-DD HH24:MI:SS.FF'),'RR') d;
+------+
| d    |
+------+
| 21   |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-02-04 11:15:16.987654321','YYYY-MM-DD HH24:MI:SS.FF'),'RRRR') d;
+------+
| d    |
+------+
| 2021 |
+------+
1 row in set (Elapsed: 00:00:00.00)

带逗号的年:Y,YYY

gbase> select to_char(to_date('2021-02-04 11:15:16.987654321','YYYY-MM-DD HH24:MI:SS.FF'),'Y,YYY') d;
+-------+
| d     |
+-------+
| 2,021 |
+-------+
1 row in set (Elapsed: 00:00:00.00)

年度标识:AD、BC

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'B.C.') d;
+------+
| d    |
+------+
| A.D. |
+------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select to_char(to_date('021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'B.C.') d;
+------+
| d    |
+------+
| A.D. |
+------+
1 row in set (Elapsed: 00:00:00.00)

月份

数字月份(01-12):MM

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'MM') d;
+------+
| d    |
+------+
| 01   |
+------+
1 row in set (Elapsed: 00:00:00.00)

全长首字母大写月份:MONTH、Month、month

和Oracle不同，GBase全部为首字母大写的结果。

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'MONTH') d;
+-----------+
| d         |
+-----------+
| January   |
+-----------+
1 row in set (Elapsed: 00:00:00.00)

gbase>
gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'Month') d;
+-----------+
| d         |
+-----------+
| January   |
+-----------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'month') d;
+-----------+
| d         |
+-----------+
| January   |
+-----------+
1 row in set (Elapsed: 00:00:00.00)

缩写首字母大写月份：MON、Mon、mon

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'MON') d;
+------+
| d    |
+------+
| Jan  |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'Mon') d;
+------+
| d    |
+------+
| Jan  |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'mon') d;
+------+
| d    |
+------+
| Jan  |
+------+
1 row in set (Elapsed: 00:00:00.00)

罗马数字的月份:RM、rm

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'RM') d;
+------+
| d    |
+------+
| II   |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'rm') d;
+------+
| d    |
+------+
| II   |
+------+
1 row in set (Elapsed: 00:00:00.00)

日期

一年中的第几天:DDD

gbase> select to_char(to_date('2021-02-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'DDD') d;
+------+
| d    |
+------+
| 035  |
+------+
1 row in set (Elapsed: 00:00:00.00)

一个月中的第几天:DD 01-31

gbase> select to_char(to_date('2021-02-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'DD') d;
+------+
| d    |
+------+
| 04   |
+------+
1 row in set (Elapsed: 00:00:00.00)

小时

12小时制的:HH、HH12

超过12时会变成下午，包括12时。

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'HH') d;
+------+
| d    |
+------+
| 02   |
+------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char(to_date('2021-01-04 12:15:16','YYYY-MM-DD HH24:MI:SS'),'HH') d;
+------+
| d    |
+------+
| 12   |
+------+
1 row in set (Elapsed: 00:00:00.00)

24小时制的:HH24

gbase> select to_char(to_date('2021-01-04 12:15:16','YYYY-MM-DD HH24:MI:SS'),'HH24') d;
+------+
| d    |
+------+
| 12   |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'HH24') d;
+------+
| d    |
+------+
| 14   |
+------+
1 row in set (Elapsed: 00:00:00.00)

上午下午:AM、PM

gbase> select to_char(to_date('2021-02-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'HHAM') d;
+------+
| d    |
+------+
| 02PM |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-02-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'HHPM') d;
+------+
| d    |
+------+
| 02PM |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'HHPM') d;
+------+
| d    |
+------+
| 11AM |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'HHpm') d;
+------+
| d    |
+------+
| 11AM |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'HHP.M.') d;
+--------+
| d      |
+--------+
| 11A.M. |
+--------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-02-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'HHP.M.') d;
+--------+
| d      |
+--------+
| 02P.M. |
+--------+
1 row in set (Elapsed: 00:00:00.00)

分钟:MI

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'MI') d;
+------+
| d    |
+------+
| 15   |
+------+
1 row in set (Elapsed: 00:00:00.00)

秒：SS

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'SS') d;
+------+
| d    |
+------+
| 16   |
+------+
1 row in set (Elapsed: 00:00:00.00)

毫秒:FF[n]

默认是6位的微秒，可以指定精度。 1<=n<=9。注意单位是秒，所以0.1秒等于100毫秒。0.123456， 精度到了毫秒。6位以后的目前只有显示用途，内部不能处理。

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'FF') d;
+--------+
| d      |
+--------+
| 000000 |
+--------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-02-04 11:15:16.987654321','YYYY-MM-DD HH24:MI:SS.FF'),'FF9') d;
+-----------+
| d         |
+-----------+
| 987654000 |
+-----------+
1 row in set (Elapsed: 00:00:00.01)
gbase> create table td(a datetime);
Query OK, 0 rows affected (Elapsed: 00:00:00.17)

gbase> insert into td values('2021-02-04 11:15:16.987654321');
ERROR 1292 (22007): Incorrect datetime value: '2021-02-04 11:15:16.987654321' for column 'a' at row 1
gbase> insert into td values('2021-02-04 11:15:16.987654');
Query OK, 1 row affected (Elapsed: 00:00:00.87)

gbase> select * from td;
+----------------------------+
| a                          |
+----------------------------+
| 2021-02-04 11:15:16.987654 |
+----------------------------+
1 row in set (Elapsed: 00:00:00.01)

世纪：CC

比如20XX年是21世纪

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'CC') d;
+------+
| d    |
+------+
| 21   |
+------+
1 row in set (Elapsed: 00:00:00.00)

季度：Q

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'Q') d;
+------+
| d    |
+------+
| 1    |
+------+
1 row in set (Elapsed: 00:00:00.00)

周数

一月里的周数:W

返回指定日期的月份里，是第几周。

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'W') d;
+------+
| d    |
+------+
| 1    |
+------+
1 row in set (Elapsed: 00:00:00.00)

一年里的周数:WW、IW

返回指定年份里的第几周,1-53/53

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'WW') d;
+------+
| d    |
+------+
| 05   |
+------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char(to_date('2021-02-04 11:15:16.987654321','YYYY-MM-DD HH24:MI:SS.FF'),'IW') d;
+------+
| d    |
+------+
| 05   |
+------+
1 row in set (Elapsed: 00:00:00.00)

一个星期中的第几天:D

测试发现，和dayofweek相同，星期日是1。

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'D') d;
+------+
| d    |
+------+
| 2    |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'day') d;
+-----------+
| d         |
+-----------+
| Monday    |
+-----------+
1 row in set (Elapsed: 00:00:00.00)

全长首字母大写的星期几：DAY、Day、day

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'Day') d;
+-----------+
| d         |
+-----------+
| Monday    |
+-----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'DAY') d;
+-----------+
| d         |
+-----------+
| Monday    |
+-----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'day') d;
+-----------+
| d         |
+-----------+
| Monday    |
+-----------+
1 row in set (Elapsed: 00:00:00.00)

缩写首字母大写星期几：DY、Dy、dy

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'DY') d;
+------+
| d    |
+------+
| Mon  |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'Dy') d;
+------+
| d    |
+------+
| Mon  |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-01-04 14:15:16','YYYY-MM-DD HH24:MI:SS'),'dy') d;
+------+
| d    |
+------+
| Mon  |
+------+
1 row in set (Elapsed: 00:00:00.00)

午夜后的秒数:SSSSS

gbase> select to_char(to_date('2021-02-04 11:15:16','YYYY-MM-DD HH24:MI:SS'),'SSSSS') d;
+-------+
| d     |
+-------+
| 40516 |
+-------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select 40516/60 /60;
+--------------+
| 40516/60 /60 |
+--------------+
|  11.25444444 |
+--------------+
1 row in set (Elapsed: 00:00:00.00)

删除开头和结尾的空格:FM

实际测试没看到效果，这个在格式化数字时很有效。

gbase> select to_char(to_date('2021-02-04 11:15:16.987654321','YYYY-MM-DD HH24:MI:SS.FF'),'FMMONTH') d;
+-----------+
| d         |
+-----------+
| February  |
+-----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select to_char(to_date('2021-02-04 11:15:16.987654321','YYYY-MM-DD HH24:MI:SS.FF'),'FM MONTH') d;
+------------+
| d          |
+------------+
|  February  |
+------------+
1 row in set (Elapsed: 00:00:00.00)

gbase>
gbase> select to_char(to_date('2021-02-04 11:15:16.987654321','YYYY-MM-DD HH24:MI:SS.FF'),' MONTHFM') d;
+------------+
| d          |
+------------+
|  February  |
+------------+
1 row in set (Elapsed: 00:00:00.00)

gbase>
gbase>
gbase>
gbase> select to_char(to_date('2021-02-04 11:15:16.987654321','YYYY-MM-DD HH24:MI:SS.FF'),'     MONTHFM') d;
+----------------+
| d              |
+----------------+
|      February  |
+----------------+
1 row in set (Elapsed: 00:00:00.00)

参考

相近功能

GBase 8a数据库函数date_format将日期格式化成指定格式的文本

反向功能

GBase 8a数据库函数str_to_date将文本按指定格式转化为日期

相关功能

GBase 8a将数字转字符串to_char函数功能介绍和样例

GBase 8a时间转字符串类型的几种方法

Post Views: 2,114

相关文章:

  1. 南大通用GBase 8a和周week有关的函数和文章汇总
  2. 南大通用GBase 8a数据库函数从日期转为天 to_days和反向从天转成日期from_days
  3. 南大通用GBase 8a OLAP分析函数group by rollup使用样例
  4. 南大通用GBase 8a通过LEAST函数返回参数列表里的最小值，与Min的区别
  5. 南大通用GBase 8a字符串函数bit_length的用法和例子
  6. 南大通用GBase 8a从字符串转日期函数to_date的介绍和例子
  7. 南大通用GBase 8a数字在二进制，八进制，10进制，16进制之间转换函数CONV介绍和例子
  8. 南大通用GBase 8a 模糊查询和正则函数regexp_replace、regexp_like
  9. 南大通用GBase 8a函数INSTR的使用样例
  10. 南大通用GBase 8a进制相关类型转换的函数汇总