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

时间:2017-07-12 ┊ 阅读:3,500 次 ┊ 标签: 经验 , 分享 , 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开始使用,全面进入win10时代

新电脑T480开始使用,全面进入win10时代

今天对于我是个好日子,终于换掉了自己用了10年的T400,尽管升级了内存换了SSD硬盘,但总感觉太老了需要换一台新电脑了。 T480,2k屏,256G SSD+500G机械双硬盘版本。 心情十分美丽,一切正常。拷贝文件进入秒传时代。 全新的win10系统,用一段时间后慢慢开始适应起来,真的可以...
阅读全文>>
不用手机号的免费邮箱ProtonMail

不用手机号的免费邮箱ProtonMail

现在国内注册个邮箱真难啊,要手机号绑定,天天广告 轻松注册了一个:guozhen@protonmail.com 用起来! 下面是ProtonMail简介: ProtonMail 是哈佛(Harvard)、麻省理工(MIT)、以及 CERN(欧洲核子物理研究所)的科学家合力研发推出的一项加密电子...
阅读全文>>
终极解决sendmail & mutt 邮件乱码问题[sh]

终极解决sendmail & mutt 邮件乱码问题[sh]

自从上次把所有站点https后,一直没怎么看服务器。这两天突然发现所有从服务器自动发出来的邮件全部乱码,实在让我强迫症发作!今天终于得空赶紧研究一翻。 系统:Centos6.2 没有附件时乱码如下: -----邮件原件----- 发件人: Kevin [mailto:admin@amkevin...
阅读全文>>
如何绕过登录抓取js动态加载网页数据[Python]

如何绕过登录抓取js动态加载网页数据[Python]

今天经历了一翻折腾,把一个需要登录网站并js动态加载的数据一一给抓下来了。 首先,登录时有cookie,我们需要把cookie保存下来,用urllib2构建request时加入header信息,这时还多了一点,虚构了浏览器信息,让服务器以为是正常的浏览器发起的请求,这样可以绕过简单的反爬虫策略...
阅读全文>>
邮件发送失败小结Deferred: Connection timed out with[Port 25]

邮件发送失败小结Deferred: Connection timed out with[Port 25]

一直在忙,尽管没有忙出什么成果,也不知道忙了啥。 周末了,终于有时间看了一下自己的小服务器,发现邮件提醒好久没有发出来了. 赶紧看了一下maillog,发现: Jul 1 15:35:02 ebs-xxx sendmail[18015]: w5UK022f017326: to=<xxx...
阅读全文>>