SQL中CONVERT转化函数的用法

来源：互联网发布：js insertafter 编辑：程序博客网时间：2024/06/05 01:16

格式:
CONVERT(data_type,expression[,style])

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

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

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

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

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