IBM DB2 SQL to_char 函数用法总结[SQL]

时间:2017-07-12 ┊ 阅读:7,124 次 ┊ 标签: 经验 , 分享 , SQL

在用sql处理数据时经常会碰到日期时间格式转换的问题,其中to_char函数有时候就会用到,特别的方便。
作为ibm提供的内置函数,功能和varchar_format差不多。
首先看一下to_char语法:

TO_CHAR(time_stamp, format_string)

to_char函数按指定格式(format_string)返回指定时间戳(time_stamp)对应的日期时间字符串

VALUES TO_CHAR(CURRENT TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')
UNION ALL
VALUES VARCHAR_FORMAT(CURRENT TIMESTAMP,'YYYY-MM-DD HH24:MI:SS');
1
2008-07-19 15:45:20
2008-07-19 15:45:20

格式字符中里只能包含如下指定的分隔符(分隔符可以放在字符串首或尾):
dash (-)

period (.)

slash (/)

comma (,)

apostrophe (')

semi-colon (;)

colon (:)

blank ( )

可用格式元素:
CC Century (00-99)
If the last two digits of the four-digit year are zero, the result is the first two digits of the year. Otherwise, the result is the first two digits of the year plus one.

DD Day of month (01-31)

DDD Day of year (001-366)

FFNo Fractional seconds (0-999999)
The number n is used to specify the number of digits to include in the returned value. Valid values for n are 1-6. The default is 6.

HH HH behaves the same as HH12

HH12 Hour of the day (01-12) in 12-hour format.

AM is the default meridian indicator.

HH24 Hour of the day (00-24) in 24-hour format.

IW ISO week of the year (01-53)
The week starts on Monday and includes seven days. Week 1 is the first week of the year to contain a Thursday, which is equivalent to the first week of the year to contain January 4.

I ISO year (0-9)
The last digit of the year based on the ISO week that is returned.

IY ISO year (00-99)
The last two digits of the year based on the ISO week that is returned.

IYY ISO year (000-999)
The last three digits of the year based on the ISO week that is returned.

IYYY ISO year (0000-9999)
The 4-digit year based on the ISO week that is returned.

J Julian day (number of days since January 1, 4713 BC)

MI Minute (00-59)

MM Month (01-12)

NNNNNN Microseconds (000000-999999). Same as FF6.

Q Quarter (1-4), where the months January through March return 1.

RR RR behaves the same as YY

RRRR RRRR behaves the same as YYYY

SS Seconds (00-59)

SSSSS Seconds since previous midnight (00000-86400)

W Week of the month (1-5)
Week 1 starts on the first day of the month and ends on the seventh day.

WW Week of the year (01-53)
Week 1 starts on January 1 and ends on January 7.

Y Last digit of the year (0-9)

YY Last two digits of the year (00-99)

YYY Last three digits of the year (000-999)

YYYY 4-digit year (0000-9999)

WITH FORMATS(FORMAT, RESULT) AS 
  (
  VALUES
  ('Date',TO_CHAR(NOW(),'YYYY-MM-DD HH24-MM-SS')),
  ('CC Century',TO_CHAR(NOW(),'CC')),
  ('DD Day of Month',TO_CHAR(NOW(),'DD')), 
  ('DDD Day of Year',TO_CHAR(NOW(),'DDD')),
  ('FFNo Microseconds',TO_CHAR(NOW(),'FF')),
  ('HH (HH12)',TO_CHAR(NOW(),'HH')),
  ('HH12 Hour of the Day',TO_CHAR(NOW(),'HH12')),
  ('HH24 Hour of the Day',TO_CHAR(NOW(),'HH24')),  
  ('IW ISO Week of the Year',TO_CHAR(NOW(),'IW')),
  ('I ISO Year',TO_CHAR(NOW(),'I')),
  ('IY ISO Year',TO_CHAR(NOW(),'IY')),
  ('IYY ISO Year',TO_CHAR(NOW(),'IYY')),
  ('IYYY ISO Year',TO_CHAR(NOW(),'IYYY')),
  ('J Julian Day',TO_CHAR(NOW(),'J')),
  ('MI Minute',TO_CHAR(NOW(),'MI')),
  ('MM Month',TO_CHAR(NOW(),'MM')),
  ('NNNNNN Microseconds',TO_CHAR(NOW(),'NNNNNN')),
  ('Q Quarter (1-4)',TO_CHAR(NOW(),'Q')),
  ('RR Same as YY',TO_CHAR(NOW(),'RR')),
  ('RRRR Same as YYYY',TO_CHAR(NOW(),'RRRR')),
  ('SS Seconds',TO_CHAR(NOW(),'SS')),
  ('SSSSS Seconds',TO_CHAR(NOW(),'SSSSS')),
  ('W Week of the Month',TO_CHAR(NOW(),'W')),
  ('WW Week of the Year',TO_CHAR(NOW(),'WW')),
  ('Y Last digit of the Year',TO_CHAR(NOW(),'Y')),
  ('YY Last 2 Digits of the Year',TO_CHAR(NOW(),'YY')),
  ('YYY Last 3 Digits of the Year',TO_CHAR(NOW(),'YYY')),
  ('YYYY 4-digit Year',TO_CHAR(NOW(),'YYYY'))
  )
SELECT * FROM FORMATS

格式和结果详细列表:
FORMAT RESULT
Date 2008-07-19 16-07-21
CC Century 21
DD Day of Month 19
DDD Day of Year 201
FFNo Microseconds 675000
HH (HH12) 04
HH12 Hour of the Day 04
HH24 Hour of the Day 16
IW ISO Week of the Year 29
I ISO Year 8
IY ISO Year 08
IYY ISO Year 008
IYYY ISO Year 2008
J Julian Day 2454667
MI Minute 47
MM Month 07
NNNNNN Microseconds 675000
Q Quarter (1-4) 3
RR Same as YY 08
RRRR Same as YYYY 2008
SS Seconds 21
SSSSS Seconds 60441
W Week of the Month 3
WW Week of the Year 29
Y Last Digit of the Year 8
YY Last 2 Digits of the Year 08
YYY Last 3 Digits of the Year 008
YYYY 4-Digit Year 2008

带分隔符:

WITH FORMATS(FORMAT,RESULT) AS
  (
  VALUES
  ('DD/MM/YY',TO_CHAR(NOW(),'DD/MM/YY')),
  ('YYYY-MM-DD',TO_CHAR(NOW(),'YYYY-MM-DD')), 
  ('YYYY.DDD',TO_CHAR(NOW(),'YYYY.DDD')),
  ('YYYYMMDD',TO_CHAR(NOW(),'YYYYMMDD')),
  ('HH.MI',TO_CHAR(NOW(),'HH.MI')),
  ('HH:MM:SS',TO_CHAR(NOW(),'HH:MM:SS'))
  )
SELECT * FROM FORMATS

FORMAT RESULT
DD/MM/YY 19/07/08
YYYY-MM-DD 2008-07-19
YYY.DDD 2008.201
YYYYMMDD 20080719
HH.MI 04.47
HH:MM:SS 04:07:21

文章评论

添加新评论

温馨提醒:如果您是第一次在本站留言,需要审核后才能显示哦!

相关文章

T480自动关机无法启动(解决办法)

T480自动关机无法启动(解决办法)

周五升级更新后,用一会就关机,还开不了,怎么按开机键也没有反应。 周六折腾一上午,拔电源,断电,20秒复位,把电池都拿下来了。都不行。 下午实在没办法了,给售后打电话让拿去检测一下。 然后拿售后去了,给检测一会没问题,说内存不干净给擦一下好了,开机正常了。 付款50块检测费,开心的拿回家,一...
阅读全文>>
Office 2016 Client Software License Management Tool

Office 2016 Client Software License Management Tool

Office 2016 Client Software License Management Tool Usage cscript ospp.vbs /Option:Value ComputerName User Password ComputerName: Name of remote c...
阅读全文>>
office2016 产品已激活 却同时显示需要激活(解决办法)

office2016 产品已激活 却同时显示需要激活(解决办法)

直接上命令吧: c:\Program Files\Microsoft Office\Office16>cscript.exe ospp.vbs /dstatus Microsoft (R) Windows Script Host Version 5.812 版权所有(C) Microso...
阅读全文>>
好久不弄代码,求急之下改了一本老程序

好久不弄代码,求急之下改了一本老程序

前两天着急情况下改写了一本老程序 嗯,RPG程序 几十年前写的程序 实现的东西很简单,原来画面上展现的一个表,现在需要批量的打印出来给业务看 我需要把所有画面功能隐藏 我直接把原生代码copy到自己lib里 然后把所有和画面相关元素删除 入口参数删除 清理掉所有无用数据文件 清理掉为画面定义的...
阅读全文>>
去除开始菜单ms-resource:appname/text幽灵快捷方式

去除开始菜单ms-resource:appname/text幽灵快捷方式

之前发现电脑菜单出现了这个无效的快捷方式没当回事,后来发现一直在,还没有用。尝试了各种方法,都无法删除掉。 最后终于找到了终极方法: 方法一: 以管理员模式运行PowerShell 然后在PowerShell粘上如下命令并回车: Get-AppxPackage -all Holographi...
阅读全文>>