您现在的位置是:首页 > SQLServer

SQLServer

CONVERT转化函数的用法

2020-11-20 10:39:12 SQLServer admin
SQL中CONVERT转化函数的用法添加时间:2007-4-22 格式:CONVERT(data_type,expression[,style])说明:此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nch
SQL中CONVERT转化函数的用法
添加时间:2007-4-22 
格式:
CONVERT(data_type,expression[,style])

说明:
此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)
相互转换的时候才用到.

例子:
SELECTCONVERT(varchar(30),getdate(),101)now
结果为:
now
---------------------------------------
|09/15/2001

===================================================================

style数字在转换时间时的含义如下:

------------------------------------------------------------------------------------------------------------
Style(2位表示年份)|Style(4位表示年份)|输入输出格式
------------------------------------------------------------------------------------------------------------
0|100|monddyyyyhh:miAM(或PM)
------------------------------------------------------------------------------------------------------------
1|101美国|mm/dd/yy
------------------------------------------------------------------------------------------------------------
2|102ANSI|yy-mm-dd
------------------------------------------------------------------------------------------------------------
3|103英法|dd/mm/yy
------------------------------------------------------------------------------------------------------------
4|104德国|dd.mm.yy
------------------------------------------------------------------------------------------------------------
5|105意大利|dd-mm-yy
------------------------------------------------------------------------------------------------------------
6|106|ddmonyy
------------------------------------------------------------------------------------------------------------
7|107|mondd,yy
------------------------------------------------------------------------------------------------------------
8|108|hh:mm:ss
------------------------------------------------------------------------------------------------------------
9|109|monddyyyyhh:mi:ss:mmmmAM(或PM)
------------------------------------------------------------------------------------------------------------
10|110美国|mm-dd-yy
------------------------------------------------------------------------------------------------------------
11|111日本|yy/mm/dd
------------------------------------------------------------------------------------------------------------
12|112ISO|yymmdd
------------------------------------------------------------------------------------------------------------
13|113欧洲默认值|ddmonyyyyhh:mi:ss:mmm(24小时制)
------------------------------------------------------------------------------------------------------------
14|114|hh:mi:ss:mmm(24小时制)
------------------------------------------------------------------------------------------------------------
20|120ODBC规范|yyyy-mm-ddhh:mi:ss(24小时制)
------------------------------------------------------------------------------------------------------------
21|121|yyyy-mm-ddhh:mi:ss:mmm(24小时制)
------------------------------------------------------------------------------------------------------------

语句及查询结果:
SELECTCONVERT(varchar(100),GETDATE(),0):0516200610:57AM
SELECTCONVERT(varchar(100),GETDATE(),1):05/16/06
SELECTCONVERT(varchar(100),GETDATE(),2):06.05.16
SELECTCONVERT(varchar(100),GETDATE(),3):16/05/06
SELECTCONVERT(varchar(100),GETDATE(),4):16.05.06
SELECTCONVERT(varchar(100),GETDATE(),5):16-05-06
SELECTCONVERT(varchar(100),GETDATE(),6):160506
SELECTCONVERT(varchar(100),GETDATE(),7):0516,06
SELECTCONVERT(varchar(100),GETDATE(),8):10:57:46
SELECTCONVERT(varchar(100),GETDATE(),9):0516200610:57:46:827AM
SELECTCONVERT(varchar(100),GETDATE(),10):05-16-06
SELECTCONVERT(varchar(100),GETDATE(),11):06/05/16
SELECTCONVERT(varchar(100),GETDATE(),12):060516
SELECTCONVERT(varchar(100),GETDATE(),13):1605200610:57:46:937
SELECTCONVERT(varchar(100),GETDATE(),14):10:57:46:967
SELECTCONVERT(varchar(100),GETDATE(),20):2006-05-1610:57:47
SELECTCONVERT(varchar(100),GETDATE(),21):2006-05-1610:57:47.157
SELECTCONVERT(varchar(100),GETDATE(),22):05/16/0610:57:47AM
SELECTCONVERT(varchar(100),GETDATE(),23):2006-05-16
SELECTCONVERT(varchar(100),GETDATE(),24):10:57:47
SELECTCONVERT(varchar(100),GETDATE(),25):2006-05-1610:57:47.250
SELECTCONVERT(varchar(100),GETDATE(),100):0516200610:57AM
SELECTCONVERT(varchar(100),GETDATE(),101):05/16/2006
SELECTCONVERT(varchar(100),GETDATE(),102):2006.05.16
SELECTCONVERT(varchar(100),GETDATE(),103):16/05/2006
SELECTCONVERT(varchar(100),GETDATE(),104):16.05.2006
SELECTCONVERT(varchar(100),GETDATE(),105):16-05-2006
SELECTCONVERT(varchar(100),GETDATE(),106):16052006
SELECTCONVERT(varchar(100),GETDATE(),107):0516,2006
SELECTCONVERT(varchar(100),GETDATE(),108):10:57:49
SELECTCONVERT(varchar(100),GETDATE(),109):0516200610:57:49:437AM
SELECTCONVERT(varchar(100),GETDATE(),110):05-16-2006
SELECTCONVERT(varchar(100),GETDATE(),111):2006/05/16
SELECTCONVERT(varchar(100),GETDATE(),112):20060516
SELECTCONVERT(varchar(100),GETDATE(),113):1605200610:57:49:513
SELECTCONVERT(varchar(100),GETDATE(),114):10:57:49:547
SELECTCONVERT(varchar(100),GETDATE(),120):2006-05-1610:57:49
SELECTCONVERT(varchar(100),GETDATE(),121):2006