SQL日期函数
SQL日期函数中的类型码可以为0,1,2,3,4,5,6,7,8,9,10,11,12,13,14
,20,21,22,23,24,25,100,101,102,103,104,105,106,107,108,109,100,
110,111,112,113,114,120,121,126,127,130,131
函数 | 结果 |
---|---|
select CONVERT(varchar(30),getdate(),0) | 09 13 2017 3:51PM |
select CONVERT(varchar(30),getdate(),1) | 09/13/17 |
select CONVERT(varchar(30),getdate(),2) | 17.09.13 |
select CONVERT(varchar(30),getdate(),3) | 13/09/17 |
select CONVERT(varchar(30),getdate(),4) | 13.09.17 |
select CONVERT(varchar(30),getdate(),5) | 13-09-17 |
select CONVERT(varchar(30),getdate(),6) | 13 09 17 |
select CONVERT(varchar(30),getdate(),7) | 09 13, 17 |
select CONVERT(varchar(30),getdate(),8) | 15:51:54 |
select CONVERT(varchar(30),getdate(),9) | 09 13 2017 3:51:54:550PM |
select CONVERT(varchar(30),getdate(),10) | 09-13-17 |
select CONVERT(varchar(30),getdate(),11) | 17/09/13 |
select CONVERT(varchar(30),getdate(),12) | 170913 |
select CONVERT(varchar(30),getdate(),13) | 13 09 2017 15:51:54:550 |
select CONVERT(varchar(30),getdate(),14) | 15:51:54:550 |
select CONVERT(varchar(30),getdate(),20) | 2017-09-13 15:51:54 |
select CONVERT(varchar(30),getdate(),21) | 2017-09-13 15:51:54.550 |
select CONVERT(varchar(30),getdate(),22) | 09/13/17 3:51:54 PM |
select CONVERT(varchar(30),getdate(),23) | 2017-09-13 |
select CONVERT(varchar(30),getdate(),24) | 15:51:54 |
select CONVERT(varchar(30),getdate(),25) | 2017-09-13 15:51:54.550 |
select CONVERT(varchar(30),getdate(),100) | 09 13 2017 3:51PM |
select CONVERT(varchar(30),getdate(),101) | 09/13/2017 |
select CONVERT(varchar(30),getdate(),102) | 2017.09.13 |
select CONVERT(varchar(30),getdate(),103) | 13/09/2017 |
select CONVERT(varchar(30),getdate(),104) | 13.09.2017 |
select CONVERT(varchar(30),getdate(),105) | 13-09-2017 |
select CONVERT(varchar(30),getdate(),106) | 13 09 2017 |
select CONVERT(varchar(30),getdate(),107) | 09 13, 2017 |
select CONVERT(varchar(30),getdate(),108) | 15:51:54 |
select CONVERT(varchar(30),getdate(),109) | 09 13 2017 3:51:54:550PM |
select CONVERT(varchar(30),getdate(),110) | 09-13-2017 |
select CONVERT(varchar(30),getdate(),111) | 2017/09/13 |
select CONVERT(varchar(30),getdate(),112) | 20170913 |
select CONVERT(varchar(30),getdate(),113) | 13 09 2017 15:51:54:550 |
select CONVERT(varchar(30),getdate(),114) | 15:51:54:550 |
select CONVERT(varchar(30),getdate(),120) | 2017-09-13 15:51:54 |
select CONVERT(varchar(30),getdate(),121) | 2017-09-13 15:51:54.550 |
select CONVERT(varchar(30),getdate(),126) | 2017-09-13T15:51:54.550 |
select CONVERT(varchar(30),getdate(),127) | 2017-09-13T15:51:54.550 |
select CONVERT(varchar(30),getdate(),130) | 22 ?? ????? 1438 3:51:54:550P |
select CONVERT(varchar(30),getdate(),131) | 22/12/1438 3:51:54:550PM |
其中,21,25,121效果相同(2017-09-13 15:51:54.550 )。
其中,在select语句中需要获取固定格式日期时,只需将getdate()替换成需要查找的字段名,需要设置的该字段的别名直接在后面添加即可,
如:select CONVERT(varchar(30), RQ_REQ_DATE, 21) from td.REQ;
查询结果: