冰峰雪晴

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

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

本文固定链接: https://www.amkevin.com/293.html｜冰峰雪晴｜转载请注明出处,谢谢