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

时间:2017-07-12 ┊ 阅读:12,213 次 ┊ 标签: 经验 , 分享 , 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

文章评论

添加新评论

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

相关文章

网页路径中的'.'和'..'还有'./'和'../'区别

网页路径中的'.'和'..'还有'./'和'../'区别

. 表示当前目录 .. 表示当前目录的上一级目录。 ./表示当前目录下的某个文件或文件夹,视后面跟着的名字而定 ../表示当前目录上一级目录的文件或文件夹,视后面跟着的名字而定。 例如: 文件夹 a 下面有 文件夹b c 和文件 d。 文件夹b下面有e.php 和文件f。 则e中的 . 表示...
阅读全文>>
Introduction to ILE RPG Activation Groups

Introduction to ILE RPG Activation Groups

Learn how activation groups can help your ILE RPG programs run more efficiently, how to specify the type of group to use, and closing and reclaimin...
阅读全文>>
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...
阅读全文>>