Sybase ASE convert函数中的style日期时间样式
convert函数的style截止到ASE15.0.3有以下的样式!
Date format conversions using the style parameter
|
Without century (yy) |
With century (yyyy) |
Standard |
Output |
|
- |
0 or 100 |
Default |
mon dd yyyy hh:mm AM (or PM) |
|
1 |
101 |
USA |
mm/dd/yy |
|
2 |
2 |
SQL standard |
yy.mm.dd |
|
3 |
103 |
English/French |
dd/mm/yy |
|
4 |
104 |
German |
dd.mm.yy |
|
5 |
105 |
dd-mm-yy |
|
|
6 |
106 |
dd mon yy |
|
|
7 |
107 |
mon dd, yy |
|
|
8 |
108 |
HH:mm:ss |
|
|
- |
9 or 109 |
Default + milliseconds |
mon dd yyyy hh:mm:ss AM (or PM) |
|
10 |
110 |
USA |
mm-dd-yy |
|
11 |
111 |
Japan |
yy/mm/dd |
|
12 |
112 |
ISO |
yymmdd |
|
13 |
113 |
yy/dd/mm |
|
|
14 |
114 |
mm/yy/dd |
|
|
14 |
114 |
hh:mi:ss:mmmAM(or PM) |
|
|
15 |
115 |
dd/yy/mm |
|
|
- |
16 or 116 |
mon dd yyyy HH:mm:ss |
|
|
17 |
117 |
hh:mmAM |
|
|
18 |
118 |
HH:mm |
|
|
19 |
hh:mm:ss:zzzAM |
||
|
20 |
hh:mm:ss:zzz |
||
|
21 |
yy/mm/dd HH:mm:ss |
||
|
22 |
yy/mm/dd HH:mm AM (or PM) |
||
|
23 |
yyyy-mm-ddTHH:mm:ss |
更新：2021-09-23
ASE 16.0 SP02 新增日期时间样式：
|
Without century (yy) |
With century (yyyy) |
Standard |
Output |
| 36 | 136 |
<hh:mm:ss.zzzzzz>AM (PM) |
|
| 37 | 137 |
<hh:mm.ss.zzzzzz> | |
| 38 |
138 |
<mon dd yyyy hh:mm:ss.zzzzzz>AM (PM) | |
| 39 |
139 |
<mon dd yyyy hh:mm:ss.zzzzzz> | |
|
40 |
140 |
<yyyy-mm-dd hh:mm:ss.zzzzzz> |
The default values (style 0 or 100), and style 9 or 109 return the century (yyyy). When converting to char or varchar from smalldatetime, styles that include seconds or milliseconds show zeros in those positions.
当前日期为：2010-9-24
|
样式 |
结果 |
|
select convert(varchar,getdate(),0) |
Sep 24 2010 5:54AM |
|
select convert(varchar,getdate(),1) |
09/24/10 |
|
select convert(varchar,getdate(),2) |
10.09.24 |
|
select convert(varchar,getdate(),3) |
24/09/10 |
|
select convert(varchar,getdate(),4) |
24.09.10 |
|
select convert(varchar,getdate(),5) |
24-09-10 |
|
select convert(varchar,getdate(),6) |
24 Sep 10 |
|
select convert(varchar,getdate(),7) |
Sep 24, 10 |
|
select convert(varchar,getdate(),100) |
Sep 24 2010 5:55AM |
|
select convert(varchar,getdate(),101) |
09/24/2010 |
|
select convert(varchar,getdate(),102) |
2010.09.24 |
|
select convert(varchar,getdate(),103) |
24/09/2010 |
|
select convert(varchar,getdate(),104) |
24.09.2010 |
|
select convert(varchar,getdate(),105) |
24-09-2010 |
|
select convert(varchar,getdate(),106) |
24 Sep 2010 |
|
select convert(varchar,getdate(),107) |
Sep 24, 2010 |
|
|
|
|
select convert(varchar,getdate(),8) |
05:58:42 |
|
select convert(varchar,getdate(),9) |
Sep 24 2010 5:58:48:640AM |
|
select convert(varchar,getdate(),108) |
05:59:09 |
|
select convert(varchar,getdate(),109) |
Sep 24 2010 5:59:27:606AM |
|
|
|
|
select convert(varchar,getdate(),10) |
09-24-10 |
|
select convert(varchar,getdate(),11) |
10/09/24 |
|
select convert(varchar,getdate(),12) |
100924 |
|
select convert(varchar,getdate(),13) |
10/24/09 |
|
select convert(varchar,getdate(),14) |
09/10/24 |
|
select convert(varchar,getdate(),15) |
24/10/09 |
|
select convert(varchar,getdate(),16) |
Sep 24 2010 06:02:21 |
|
select convert(varchar,getdate(),17) |
6:02AM |
|
select convert(varchar,getdate(),18) |
06:02 |
|
select convert(varchar,getdate(),19) |
6:02:51:840AM |
|
select convert(varchar,getdate(),20) |
06:03:07:373 |
|
select convert(varchar,getdate(),21) |
10/09/24 06:03:35 |
|
select convert(varchar,getdate(),22) |
10/09/24 6:03AM |
|
select convert(varchar,getdate(),23) |
2010-09-24T06:03:50 |
|
|
|
|
select convert(varchar,getdate(),100) |
Sep 24 2010 6:04AM |
|
select convert(varchar,getdate(),101) |
09/24/2010 |
|
select convert(varchar,getdate(),102) |
2010.09.24 |
|
select convert(varchar,getdate(),103) |
24/09/2010 |
|
select convert(varchar,getdate(),104) |
24.09.2010 |
|
select convert(varchar,getdate(),15) |
24-09-2010 |
|
select convert(varchar,getdate(),16) |
24 Sep 2010 |
|
select convert(varchar,getdate(),107) |
Sep 24, 2010 |
|
select convert(varchar,getdate(),108) |
06:06:13 |
|
select convert(varchar,getdate(),109) |
Sep 24 2010 6:06:20:826AM |
|
select convert(varchar,getdate(),110) |
09-24-2010 |
|
select convert(varchar,getdate(),111) |
2010/09/24 |
|
select convert(varchar,getdate(),112) |
20100924 |
|
select convert(varchar,getdate(),113) |
2010/24/09 |
|
select convert(varchar,getdate(),114) |
09/2010/24 |
|
select convert(varchar,getdate(),115) |
24/2010/09 |
|
select convert(varchar,getdate(),116) |
Sep 24 2010 06:08:10 |
|
select convert(varchar,getdate(),117) |
2010/09/24 06:08:27 |
|
select convert(varchar,getdate(),118) |
2010/09/24 6:08AM |
例子：
显示格式为： YYYY-MM-DD HH:MI 的日期，如： 2011-09-24 13:48
使用SQL语句：
select str_replace( convert( varchar, getdate(), 102), '.', '-') + ' ' + convert( varchar, getdate(), 18)
更新：2021-09-23 使用ASE 15.7 SP140 HF1验证。
|
样式 |
结果 |
|
select convert(varchar,getdate(),36) |
11:46:12.806000AM |
|
select convert(varchar,getdate(),37) |
11:46:49.753000 |
|
select convert(varchar,getdate(),38) |
Sep 23 21 11:47:06.113000AM |
|
select convert(varchar,getdate(),39) |
Sep 23 21 11:47:28.163000 |
|
select convert(varchar,getdate(),40) |
21-09-23 11:47:55.230000 |
|
select convert(varchar,getdate(),136) |
11:48:21.833000AM |
|
select convert(varchar,getdate(),137) |
11:48:40.553000 |
|
select convert(varchar,getdate(),138) |
Sep 23 2021 11:48:58.053000AM |
|
select convert(varchar,getdate(),139) |
Sep 23 2021 11:49:12.766000 |
|
select convert(varchar,getdate(),140) |
2021-09-23 11:49:33.163000 |
|
select convert(varchar,getdate(),141) |
Sep 23 2021 11:49:51.533000AM |
发现在ASE 15.7 SP140 HF1上样式117和118与ASE 15.0.3上的不同。
在ASE 15.7 SP140 HF1上执行：
1> select convert(varchar,getdate(),117)
2> go
------------------------------------------------------------
11:52AM
(1 row affected)
1> select convert(varchar,getdate(),118)
2> go
------------------------------------------------------------
11:52
(1 row affected)
在ASE 15.0.3上执行：
1> select convert(varchar,getdate(),117)
2> go
------------------------------
2021/09/23 11:54:18
(1 row affected)
1> select convert(varchar,getdate(),118)
2> go
------------------------------
2021/09/23 11:54AM
(1 row affected)
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：ASE convert style 日期样式
————————————————————————————————-