您的位置:新葡亰496net > 网络数据库 > 数据类型转换,Convert数据类型转换

数据类型转换,Convert数据类型转换

发布时间:2019-08-16 20:00编辑:网络数据库浏览(103)

    一、概述

    本篇文章转载来着官网在线文档,文章主要介绍SQL Server数据类型转换相关语法、隐式转换、Date样式等。

     

    新葡亰496net 1语法

    Syntax for CAST:
    CAST ( expression AS data_type [ ( length ) ] )
    
    Syntax for CONVERT:
    CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
    

    参数

    expression:任何有效的表达式。

    data_type:目标数据类型。这包括 xmlbigintsql_variant。不能使用别名数据类型。有关可用数据类型的详细信息,请参阅数据类型 (Transact-SQL)。

    length:指定目标数据类型长度的可选整数。默认值为 30。

    style:指定 CONVERT 函数如何转换 expression 的整数表达式。如果样式为 NULL,则返回 NULL。该范围是由 data_type 确定的。有关详细信息,请参阅“备注”部分。

    1、数据类型转换:

    • (1)两种转换场合:
    • A:当两个结果集之间的数据进行比较或联和(union)的时候,如果两个结果集的数据类型不同时必须进行转换。
    • B:将Transact-SQL 得结果集返回给程序中的变量时,须将结果集的类型从SQL Server数据类型转换为变量的数据类型。
    • (2)两种转换方式:
    • A:自动进行的隐性转换(对于用户是不可见的)。 如:一个 smallint 变量和 int 变量比较时,将smallint 变量在比较前被隐性转换成 int 变量。
    • B:用户提供的显式转换(使用 CAST 或 CONVERT 函数进行显式转换)。

    注意

    A:目标数据类型不能使用别名数据类型。 nchar、nvarchar、char、varchar、binary 或 varbinary

    B:目标数据格式的样式在下列类型间转换时才有效

    (1)datetime 或 smalldatetime 数据转换成字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型)

    (2)已知日期或时间格式的字符数据转换成 datetime 或 smalldatetime 数据,或是字符串格式

    (3)用于将 float、real、money 或 smallmoney 数据转换成字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型)。

    经常会用到,一直没留底,今天找了一段来,以供参考

     

    将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。

    Date 和 Time 样式

    如果 expression 为 date 或 time 数据类型,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。SQL Server 使用科威特算法来支持阿拉伯样式的日期格式。

    不带世纪数位 (yy) (1) 带世纪数位 (yyyy) 标准 输入/输出 (3)

    -

    0100 (1, 2)

    默认

    mon dd yyyy hh:miAM(或 PM)

    1

    101

    美国

    mm/dd/yyyy

    2

    102

    ANSI

    yy.mm.dd

    3

    103

    英国/法国

    dd/mm/yyyy

    4

    104

    德国

    dd.mm.yy

    5

    105

    意大利

    dd-mm-yy

    6

    106 (1)

    -

    dd mon yy

    7

    107 (1)

    -

    mon dd, yy

    8

    108

    -

    hh:mi:ss

    -

    9109 (1, 2)

    默认设置 毫秒

    mon dd yyyy hh:mi:ss:mmmAM(或 PM)

    10

    110

    美国

    mm-dd-yy

    11

    111

    日本

    yy/mm/dd

    12

    112

    ISO

    yymmdd

    yyyymmdd

    -

    13113 (1, 2)

    欧洲默认设置 毫秒

    dd mon yyyy hh:mi:ss:mmm(24h)

    14

    114

    -

    hh:mi:ss:mmm(24h)

    -

    20120 (2)

    ODBC 规范

    yyyy-mm-dd hh:mi:ss(24h)

    -

    21121 (2)

    ODBC 规范(带毫秒)

    yyyy-mm-dd hh:mi:ss.mmm(24h)

    -

    126 (4)

    ISO8601

    yyyy-mm-ddThh:mi:ss.mmm(无空格)

    -

    127(6, 7)

    带时区 Z 的 ISO8601。

    yyyy-mm-ddThh:mi:ss.mmmZ

    (无空格)

    -

    130 (1, 2)

    回历 (5)

    dd mon yyyy hh:mi:ss:mmmAM

    -

    131 (2)

    回历 (5)

    dd/mm/yy hh:mi:ss:mmmAM

    1 这些样式值将返回不确定的结果。包括所有 (yy)(不带世纪数位)样式和一部分 (yyyy)(带世纪数位)样式。

    2 默认值(style 010091091311320120 以及 21121)始终返回世纪数位 (yyyy)。

    3 转换为 datetime 时输入;转换为字符数据时输出。

    4 为用于 XML 而设计。对于从 datetimesmalldatetime 到字符数据的转换,其输出格式如上一个表所述。

    5 回历是有多种变体的日历系统。SQL Server 使用科威特算法。

    注意:默认情况下,SQL Server 基于截止年份 2049 年来解释两位数的年份。换言之,就是将两位数的年份 49 解释为 2049,将两位数的年份 50 解释为 1950。许多客户端应用程序(如基于自动化对象的应用程序)都使用截止年份 2030 年。SQL Server 提供了 two digit year cutoff 配置选项,可通过此选项更改 SQL Server 使用的截止年份,从而对日期进行一致处理。建议您指定四位数年份。

    6 仅支持从字符数据转换为 datetimesmalldatetime。仅表示日期或时间成分的字符数据转换为 datetimesmalldatetime 数据类型时,未指定的时间成分设置为 00:00:00.000,未指定的日期成分设置为 1900-01-01。

    7使用可选的时间区域指示符 (Z) 更便于将具有时区信息的 XML datetime 值映射到没有时区的 SQL Server datetime 值。Z 是时区 UTC-0 的指示符。其他时区则以 或 - 方向的 HH:MM 偏移量来指示。例如:2006-12-12T23:45:12-08:00

    smalldatetime 转换为字符数据时,包含秒或毫秒的样式将在这些位置上显示零。使用相应的 charvarchar 数据类型长度从 datetimesmalldatetime 值转换时,可截断不需要的日期部分。

    从样式包含时间的字符数据转换为 datetimeoffset 时,将在结果末尾追加时区偏移量。

    3、备注(来着MSDN.aspx))

     

    原文来自于: 

    语法

    使用 CAST:

    CAST ( expression AS data_type )

    使用 CONVERT:

    CONVERT (data_type[(length)], expression [, style])

    float 和 real 样式

    如果 expression 为 floatreal,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

    输出

    0(默认值)

    最多包含 6 位。根据需要使用科学记数法。

    1

    始终为 8 位值。始终使用科学记数法。

    2

    始终为 16 位值。始终使用科学记数法。

    126, 128, 129

    为了保持向后兼容而包括在内,在以后的版本中可能不推荐使用。

    (1)大值数据类型

    大值数据类型表现出与小值数据类型相同的隐式和显式转换行为,特别是 varcharnvarcharvarbinary 数据类型。但是,应该考虑以下原则:

    • imagevarbinary(max) 的转换与反向转换是隐式转换,textvarchar(max)ntextnvarchar(max) 之间的转换也是隐式转换。
    • 从大值数据类型(如 varchar(max))到小值数据类型(如 varchar)的转换是隐式转换,但如果大值相对于指定长度的小值数据类型显得太大,则产生截断。
    • varcharnvarcharvarbinary 到其相应的大值数据类型的转换都是隐式执行的。
    • sql_variant 数据类型到大值数据类型的转换是显式转换。
    • 大值数据类型不能转换为 sql_variant 数据类型。
    • ##### (2)xml 数据类型

      当您将 xml 数据类型显式或隐式转换为字符串或二进制数据类型时,xml 数据类型的内容将根据一组规则进行序列化。

      (3)文本和图像数据类型

      不支持对 textimage 数据类型进行自动数据类型转换。可将 text 数据显式转换为字符数据,将 image 数据转换为 binaryvarbinary,但最大长度是 8000 字节。如果试图进行不正确的转换,如将包含字母的字符表达式转换为 int,则 SQL Server 将返回错误消息。

      ##### (4)输出的排序规则

      如果 CAST 或 CONVERT 的输出是字符串,并且输入也是字符串,则输出将与输入具有相同的排序规则和排序规则标签。如果输入不是字符串,则输出采用数据库的默认排序规则以及强制默认的排序规则标签。请参阅排序规则优先级 (Transact-SQL).aspx)。

      若要为输出分配不同的排序规则,请将 COLLATE 子句应用于 CAST 或 CONVERT 函数的结果表达式。例如:

      SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

      ##### (5)截断结果和舍入结果

      将字符或二进制表达式(charncharnvarcharvarcharbinaryvarbinary)转换为其他数据类型的表达式时,可截断数据,仅显示部分数据,或返回错误(因为结果太短而无法显示)。除了下表显示的转换,其他到 charvarcharncharnvarcharbinaryvarbinary 的转换都将被截断。

    sql server2000中使用convert来取得datetime数据类型样式(全)

    CAST 和 CONVERT
    将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。

    参数

    expression

    是任何有效的 Microsoft® SQL Server™ 表达式。

    data_type

    目标系统所提供的数据类型,包括 bigint 和 sql_variant。不能使用用户定义的数据类型

    length

    nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。

    style

    日期格式样式,借以将 datetime 或 smalldatetime 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型);或者字符串格式样式,借以将 float、real、money 或 smallmoney 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型)。

    SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。

    在表中,左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)。

    不带世纪数位 (yy) 带世纪数位 (yyyy)
    标准

    输入/输出**
    - 0 或 100 (*) 默认值 mon dd yyyy hh:miAM(或 PM)
    1 101 美国 mm/dd/yyyy
    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:mmmAM(或 PM)
    10 110 美国 mm-dd-yy
    11 111 日本 yy/mm/dd
    12 112 ISO yymmdd
    - 13 或 113 (*) 欧洲默认值 毫秒 dd mon yyyy hh:mm:ss:mmm(24h)
    14 114 - hh:mi:ss:mmm(24h)
    - 20 或 120 (*) ODBC 规范 yyyy-mm-dd hh:mm:ss[.fff]
    - 21 或 121 (*) ODBC 规范(带毫秒) yyyy-mm-dd hh:mm:ss[.fff]
    - 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(不含空格)
    - 130* 科威特 dd mon yyyy hh:mi:ss:mmmAM
    - 131* 科威特 dd/mm/yy hh:mi:ss:mmmAM

    *    默认值(style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121)始终返回世纪数位 (yyyy)。
    ** 当转换为 datetime 时输入;当转换为字符数据时输出。
    *** 专门用于 XML。对于从 datetime 或 smalldatetime 到 character 数据的转换,输出格式如表中所示。对于从 float、money 或 smallmoney 到 character 数据的转换,输出等同于 style 2。对于从 real 到 character 数据的转换,输出等同于 style 1。
    示例:CONVERT(char(10), s.Season_PlanDate, 102) AS 计划日期
    Example:
    1.Select Convert(Varchar(10), Cast('20070723' As DateTime), 120)
    2.用Convert或Cast进行强制转换就好了
    语法:
    convert(要转换成的类型,被转换的列或表达式)
    cast(被转换的列或表达式   AS   要转换成的类型)

    money 和 smallmoney 样式

    如果 expression 为 moneysmallmoney,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

    输出

    0(默认值)

    小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数,例如 4235.98。

    1

    小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数,例如 3,510.92。

    2

    小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数,例如 4235.9819。

    126

    转换为 char(n) 或 varchar(n) 时,等同于样式 2

    日期数据格式的处理,两个示例:

    语法
    使用 CAST:

    xml 样式

    如果 data_type 为 xml,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

    输出

    0(默认值)

    使用默认的分析行为,即放弃无用的空格,且不允许使用内部 DTD 子集。

    注意:

    转换为 xml 数据类型时,SQL Server 的无用空格处理方式不同于 XML 1.0。有关详细信息,请参阅生成 XML 实例。

    1

    保留无用空格。此样式设置将默认的 xml:space 处理方式设置为与指定了 xml:space="preserve" 的行为相同。

    2

    启用有限的内部 DTD 子集处理。

    如果启用,则服务器可使用内部 DTD 子集提供的以下信息来执行非验证分析操作。

    应用属性的默认值。

    解析并扩展内部实体引用。

    检查 DTD 内容模型以实现语法的正确性。

    分析器将忽略外部 DTD 子集。此外,不评估 XML 声明来查看 standalone 属性是设置为 yes 还是 no,而是将 XML 实例当成一个独立文档进行分析。

    3

    保留无用空格,并启用有限的内部 DTD 子集处理。

    CONVERT(varchar(16), 时间一, 20) 结果:2007-02-01  08:02/*时间一般为getdate()函数或数据表里的字段*/

    CAST ( expression AS data_type )

    二进制样式

    如果 expression 为 binary(n)varbinary(n)char(n)varchar(n),则 style 可以为下表中显示的值之一。表中没有列出的样式值将返回错误。

    输出

    0(默认值)

    将 ASCII 字符转换为二进制字节,或者将二进制字节转换为 ASCII 字符。每个字符或字节按照 1:1 进行转换。

    如果 data_type 为二进制类型,则会在结果左侧添加字符 0x。

    1, 2

    如果 data_type 为二进制类型,则表达式必须为字符表达式。expression 必须由数量为偶数的十六进制数字(0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F、a、b、c、d、e、f)组成。如果将 style 设置为 1,字符 0x 必须为表达式中的前两个字符。如果表达式中包含的字符数为奇数或者包含任何无效的字符,则会引发错误。

    如果转换后的表达式长度大于 data_type 长度,则会在右侧截断结果。

    如果固定长度 data_types 大于转换后的结果,则会在结果右侧添加零。

    如果 data_type 为字符类型,则表达式必须为二进制表达式。每个二进制字符均转换为两个十六进制字符。如果转换后的表达式长度大于 data_type 长度,则会在右侧截断结果。

    如果 data_type 为固定大小的字符类型,并且转换后的结果长度小于其 data_type 长度,则会在转换后的表达式右侧添加空格,以使十六进制数字的个数保持为偶数。

    对于 style 1,将在转换后的结果左侧添加字符 0x。

    CONVERT(varchar(10), 时间一, 23) 结果:2007-02-01  /*varchar(10)表示日期输出的格式,如果不够长会发生截取*/

    使用 CONVERT:

    隐式转换

    隐式转换指那些没有指定 CAST 或 CONVERT 函数的转换。显式转换指那些需要指定 CAST 或 CONVERT 函数的转换。以下图例显示了可对 SQL Server 系统提供的数据类型执行的所有显式和隐式数据类型转换。其中包括 xmlbigintsql_variant。不存在对 sql_variant 数据类型的赋值进行的隐式转换,但是存在转换为 sql_variant 的隐式转换。

    新葡亰496net 2

    datetimeoffset 与字符类型 charvarcharncharnvarchar 之间转换时,转换后的时区偏移量部分的 HH 和 MM 都应始终为两个数字,例如 -08:00。

    注意:因为 Unicode 数据始终使用偶数个字节,所以在 binaryvarbinary 与支持 Unicode 的数据类型之间进行转换时会使用警告。例如,以下转换不返回十六进制值 41;而是返回 4100:SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)

    语句及查询结果:

    CONVERT (data_type[(length)], expression [, style])

    大值数据类型

    大值数据类型表现出与小值数据类型相同的隐式和显式转换行为,特别是 varcharnvarcharvarbinary 数据类型。但是,应该考虑以下原则:

    • imagevarbinary(max) 的转换与反向转换是隐式转换,textvarchar(max)ntextnvarchar(max) 之间的转换也是隐式转换。

    • 从大值数据类型(如 varchar(max))到小值数据类型(如 varchar)的转换是隐式转换,但如果大值相对于指定长度的小值数据类型显得太大,则产生截断。

    • varcharnvarcharvarbinary 到其相应的大值数据类型的转换都是隐式执行的。

    • sql_variant 数据类型到大值数据类型的转换是显式转换。

    • 大值数据类型不能转换为 sql_variant 数据类型。

    有关如何转换 Microsoft .NET Framework 公共语言运行时 (CLR) 用户定义类型的信息,请参阅对用户定义类型执行操作。有关如何从 xml 数据类型进行转换的详细信息。

    Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
    Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
    Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
    Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
    Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
    Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
    Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
    Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
    Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
    Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
    Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
    Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
    Select CONVERT(varchar(100), GETDATE(), 12): 060516
    Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
    Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
    Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
    Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
    Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
    Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
    Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
    Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
    Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
    Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
    Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
    Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
    Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
    Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
    Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
    Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
    Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
    Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
    Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
    Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
    Select CONVERT(varchar(100), GETDATE(), 112): 20060516
    Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
    Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
    Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
    Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
    Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
    Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
    Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM
    

    参数
    expression

    xml 数据类型

    当您将 xml 数据类型显式或隐式转换为字符串或二进制数据类型时,xml 数据类型的内容将根据一组规则进行序列化。有关这些规则的信息,请参阅 XML 数据的序列化。有关如何从 XML 转换为 CLR 用户定义类型的信息,请参阅对用户定义类型执行操作。有关如何从其他数据类型转换到 xml 数据类型的信息。

     

    是任何有效的 Microsoft® SQL Server™ 表达式。有关更多信息,请参见表达式。

    文本和图像数据类型

    不支持对 textimage 数据类型进行自动数据类型转换。可将 text 数据显式转换为字符数据,将 image 数据转换为 binaryvarbinary,但最大长度是 8000 字节。如果试图进行不正确的转换,如将包含字母的字符表达式转换为 int,则 SQL Server 将返回错误消息。

    说明: 使用 CONVERT:
    CONVERT ( data_type [ ( length )  ] , expression [ , style ] )
    参数 expression
    是任何有效的 Microsoft®  SQL Server™ 表达式。。
    data_type
    目标系统所提供的数据类型,包括 bigint 和  sql_variant。不能使用用户定义的数据类型。 length
    nchar、nvarchar、char、varchar、binary 或  varbinary 数据类型的可选参数。
    style
    日期格式样式,借以将 datetime 或 smalldatetime  数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型);或者字符串格式样式,借以将  float、real、money 或 smallmoney 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或  nvarchar 数据类型)。
    SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。
    在表中,左侧的两列表示将  datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100,可获得包括世纪数位的四位年份  (yyyy)。

    data_type

    输出排序规则

    如果 CAST 或 CONVERT 的输出是字符串,并且输入也是字符串,则输出将与输入具有相同的排序规则和排序规则标签。如果输入不是字符串,则输出采用数据库的默认排序规则以及强制默认的排序规则标签。

    若要为输出分配不同的排序规则,请将 COLLATE 子句应用于 CAST 或 CONVERT 函数的结果表达式。例如:

    SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

    重要:

    目标系统所提供的数据类型,包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息,请参见数据类型。

    截断结果和舍入结果

    将字符或二进制表达式(charncharnvarcharvarcharbinaryvarbinary)转换为其他数据类型的表达式时,可截断数据,仅显示部分数据,或返回错误(因为结果太短而无法显示)。除了下表显示的转换,其他到 charvarcharncharnvarcharbinaryvarbinary 的转换都将被截断。

    默认情况下,SQL Server 根据截止年份 2049 解释两位数字的年份。即,两位数字的年份  49 被解释为 2049,而两位数字的年份 50 被解释为 1950。许多客户端应用程序(例如那些基于 OLE 自动化对象的客户端应用程序)都使用 2030  作为截止年份。SQL Server 提供一个配置选项("两位数字的截止年份"),借以更改 SQL Server  所使用的截止年份并对日期进行一致性处理。然而最安全的办法是指定四位数字年份。

    length

     

    被转换的数据类型 转换为的数据类型 结果

    intsmallinttinyint

    char

    *

     

    varchar

    *

     

    nchar

    E

     

    nvarchar

    E

    moneysmallmoneynumericdecimalfloatreal

    char

    E

     

    varchar

    E

     

    nchar

    E

     

    nvarchar

    E

    * = 结果长度太短而无法显示。E = 因为结果长度太短无法显示而返回错误。

    SQL Server 仅保证往返转换(即从原始数据类型进行转换后又返回原始数据类型的转换)在各版本间产生相同值。以下示例显示的即是这样的往返转换:

    DECLARE @myval decimal (5, 2)
    SET @myval = 193.57
    SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
    -- Or, using CONVERT
    SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))
    

    注意:不要尝试构造 binary 值然后将其转换为数值数据类型类别的一种数据类型。SQL Server 不能保证 decimalnumeric 数据类型到 binary 的转换结果在 SQL Server 的各个版本中都相同。

    转换小数位数不同的数据类型时,结果值有时被截断,有时被舍入。下表显示了此行为。

    被转换的数据类型 转换到的数据类型 行为

    numeric

    numeric

    舍入

    numeric

    int

    截断

    numeric

    money

    舍入

    money

    int

    舍入

    money

    numeric

    舍入

    float

    int

    截断

    float

    numeric

    舍入

    float

    datetime

    舍入

    datetime

    int

    舍入

    例如,以下转换的结果为 10

    SELECT CAST(10.6496 AS int)
    

    在进行数据类型转换时,若目标数据类型的小数位数小于源数据类型的小数位数,则该值将被截断。例如,以下转换的结果为 $10.3497

    SELECT CAST(10.3496847 AS money)
    

    当非数字型 charncharvarcharnvarchar 数据转换为 intfloatnumericdecimal 时,SQL Server 将返回错误消息。当空字符串 (" ") 转换为 numericdecimal 时,SQL Server 也返回错误。

     

     

    新葡亰496net,备注:文章转载来自官方在线文档。

     

     

    备注:

        作者:pursuer.chen

        博客:http://www.cnblogs.com/chenmh

    本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。

    《欢迎交流讨论》

     

    当从 smalldatetime  转换为字符数据时,包含秒或毫秒的样式将在这些位置上显示零。当从 datetime 或 smalldatetime 值进行转换时,可以通过使用适当的 char 或  varchar 数据类型长度来截断不需要的日期部分。

    nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。

    style

    日期格式样式,借以将 datetime 或 smalldatetime 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型);或者字符串格式样式,借以将 float、real、money 或 smallmoney 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型)。

    SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。

    在表中,左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)。

    不带世纪数位 (yy) 带世纪数位 (yyyy)
    标准
    输入/输出**

    • 0 或 100 (*) 默认值 mon dd yyyy hh:miAM(或 PM)
      1 101 美国 mm/dd/yyyy
      2 102 ANSI yy.mm.dd
      3 103 英国/法国 dd/mm/yy
      数据类型转换,Convert数据类型转换。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:mmmAM(或 PM)
      10 110 美国 mm-dd-yy
      11 111 日本 yy/mm/dd
      12 112 ISO yymmdd
    • 13 或 113 (*) 欧洲默认值 毫秒 dd mon yyyy hh:mm:ss:mmm(24h)
      14 114 - hh:mi:ss:mmm(24h)
    • 20 或 120 (*) ODBC 规范 yyyy-mm-dd hh:mm:ss[.fff]
    • 21 或 121 (*) ODBC 规范(带毫秒) yyyy-mm-dd hh:mm:ss[.fff]
    • 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(不含空格)
    • 130* 科威特 dd mon yyyy hh:mi:ss:mmmAM
    • 131* 科威特 dd/mm/yy hh:mi:ss:mmmAM

    * 默认值(style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121)始终返回世纪数位 (yyyy)。
    ** 当转换为 datetime 时输入;当转换为字符数据时输出。
    *** 专门用于 XML。对于从 datetime 或 smalldatetime 到 character 数据的转换,输出格式如表中所示。对于从 float、money 或 smallmoney 到 character 数据的转换,输出等同于 style 2。对于从 real 到 character 数据的转换,输出等同于 style 1。

     

    重要 默认情况下,SQL Server 根据截止年份 2049 解释两位数字的年份。即,两位数字的年份 49 被解释为 2049,而两位数字的年份 50 被解释为 1950。许多客户端应用程序(例如那些基于 OLE 自动化对象的客户端应用程序)都使用 2030 作为截止年份。SQL Server 提供一个配置选项("两位数字的截止年份"),借以更改 SQL Server 所使用的截止年份并对日期进行一致性处理。然而最安全的办法是指定四位数字年份。

    当从 smalldatetime 转换为字符数据时,包含秒或毫秒的样式将在这些位置上显示零。当从 datetime 或 smalldatetime 值进行转换时,可以通过使用适当的 char 或 varchar 数据类型长度来截断不需要的日期部分。

    下表显示了从 float 或 real 转换为字符数据时的 style 值。

    值 输出
    0(默认值) 最大为 6 位数。根据需要使用科学记数法。
    1 始终为 8 位值。始终使用科学记数法。
    2 始终为 16 位值。始终使用科学记数法。

    在下表中,左列表示从 money 或 smallmoney 转换为字符数据时的 style 值。

    值 输出
    0(默认值) 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数,例如 4235.98。
    1 小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数,例如 3,510.92。
    2 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数,例如 4235.9819。

    返回类型
    返回与 data type 0 相同的值。

    注释
    隐性转换指那些没有指定 CAST 或 CONVERT 函数的转换。而显式转换指那些已指定了所需 CAST (CONVERT) 函数的转换。下面的图表显示了所有可用于 SQL Server 系统提供的数据类型的显式和隐性转换,这些数据类型包括 bigint 和 sql_variant。

     

     

    说明 因为 Unicode 数据始终使用偶数位字节,所以当在 binary 或 varbinary 数据类型与 Unicode 所支持的数据类型之间进行转换时会使用提示。例如,此转换不返回 41 的十六进制值,而是返回 4100 的十六进制值:SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)

    不支持 text 和 image 数据类型的自动数据类型转换。可以将 text 数据显式转换为字符数据,将 image 数据显性转换为 binary 或 varbinary 数据,但是最大长度为 8000。如果尝试进行不正确的转换(例如,将包含字母的字符表达式转换为 int),则 SQL Server 会产生错误信息。

    当 CAST 或 CONVERT 的输出是字符串并且输入也是字符串时,输出与输入具有相同的排序规则和排序规则标签。如果输入不是字符串,则输出采用数据库的默认排序规则及强制默认的排序规则标签。有关更多信息,请参见排序规则的优先顺序。

    若要给输出指派不同的排序规则,请将 COLLATE 子句应用到 CAST 或 CONVERT 函数的结果表达式中。例如:

    SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

    不存在有关赋值的从 sql_variant 数据类型进行的隐性转换,但是存在转换为 sql_variant 的隐性转换。

    将字符或二进制表达式(char、nchar、nvarchar、varchar、binary 或 varbinary)转换为不同数据类型的表达式时,数据可能会被截断,只显示一部分,或者因为结果太短无法显示而返回错误。除下表中所显示的转换外,转换为 char、varchar、nchar、nvarchar、binary 和 varbinary 时将被截断。

    被转换的数据类型 转换为的数据类型 结果
    int、smallint 或 tinyint char *
    varchar *
    nchar E
    nvarchar E
    money、smallmoney、numeric、decimal、float 或 real char E
    varchar E
    nchar E
    nvarchar E

    * 结果长度太短而无法显示。
    E 因为结果长度太短无法显示而返回错误。

    Microsoft SQL Server 仅保证往返转换(即,从原始数据类型进行转换后又返回原始数据类型)在各版本间产生相同值。下面的示例显示往返转换:

    DECLARE @myval decimal (5, 2)
    SET @myval = 193.57
    SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
    -- Or, using CONVERT
    SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

    例如,不要尝试构造 binary 值并将它们转换为数字数据类型分类的数据类型。SQL Server 并不保证 decimal 或 numeric 数据类型转换为 binary 的结果在 SQL Server 各版本间相同。

    下面的示例显示了由于太短而无法显示的结果表达式。

    USE pubs
    SELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))
    FROM titles
    WHERE type = 'trad_cook'

    下面是结果集:

    Title


    Onions, Leeks, and Garlic *
    Fifty Years in Buckingham *
    Sushi, Anyone? *

    (3 row(s) affected)

    当具有不同小数位数的数据类型进行转换时,值将被截断为最精确的数位。例如,SELECT CAST(10.6496 AS int) 的结果为 10。

    转换时,若目标数据类型的小数位数小于源数据类型的小数位数,则要转换的值将被四舍五入。例如,CAST(10.3496847 AS money) 的结果是 $10.3497。

    当将非数字类型的 char、nchar、varchar 或 nvarchar 数据转换为 int、float、numeric 或 decimal 时,SQL Server 将返回错误信息。当将空字符串 (" ") 转换为 numeric 或 decimal 时,SQL Server 也将返回错误信息。

    使用二进制字符串数据
    当 binary 或 varbinary 数据转换为字符数据并且在 x 后面指定了奇数位的值时,SQL Server 在 x 后面添加 0(零)以成为偶数位值。

    二进制数据包含从 0 到 9 和从 A 到 F(或从 a 到 f)的字符,每两个字符为一组。二进制字符串必须以 0x 开头。例如,若要输入 FF,请键入 0xFF。最大值是一个 8000 字节的二进制值,每个字节的最大值都是 FF。Binary 数据类型不能用于十六进制数据,而是用于位模式。对于存储为二进制数据的十六进制数字的转换和计算结果,无法保证其准确性。

    当指定 binary 数据类型的长度时,每两个字符被算作是一个单位长度。长度 10 表示将输入 10 个双字符组。

    由 0x 表示的空二进制字符串可以储存为二进制数据。

    示例
    A. 同时使用 CAST 和 CONVERT
    每个示例都将检索书名(这些图书的截止当前销售额的第一位数字为 3),并将这些图书的 ytd_sales 转换为 char(20)。

    -- Use CAST.
    USE pubs
    GO
    SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
    FROM titles
    WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
    GO

    -- Use CONVERT.
    USE pubs
    GO
    SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
    FROM titles
    WHERE CONVERT(char(20), ytd_sales) LIKE '3%'
    GO

    下面是任一查询的结果集:

    Title ytd_sales


    Cooking with Computers: Surrep 3876
    Computer Phobic AND Non-Phobic 375
    Emotional Security: A New Algo 3336
    Onions, Leeks, and Garlic: Coo 375

    (4 row(s) affected)

    B. 使用带有算术运算符的 CAST
    下面的示例通过将总的截止当前销售额 (ytd_sales) 与每本图书的价格 (price) 相除,进行单独列计算 (Copies)。在四舍五入到最接近的整数后,此结果将转换为 int 数据类型。

    USE pubs
    GO
    SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies'
    FROM titles
    GO

    下面是结果集:

    Copies

    205
    324
    6262
    205
    102
    7440
    NULL
    383
    205
    NULL
    17
    187
    16
    204
    418
    18
    1263
    273

    (18 row(s) affected)

    C. 使用 CAST 进行串联
    下面的示例使用 CAST 数据类型转换函数来串联非字符、非二进制表达式。

    USE pubs
    GO
    SELECT 'The price is ' CAST(price AS varchar(12))
    FROM titles
    WHERE price > 10.00
    GO

    下面是结果集:


    The price is 19.99
    The price is 11.95
    The price is 19.99
    The price is 19.99
    The price is 22.95
    The price is 20.00
    数据类型转换,Convert数据类型转换。The price is 21.59
    The price is 10.95
    The price is 19.99
    The price is 20.95
    The price is 11.95
    The price is 14.99

    (12 row(s) affected)

    D. 使用 CAST 获得更多易读文本
    下面的示例在选择列表中使用 CAST 将 title 列转换为 char(50) 列,这样结果将更加易读。

    USE pubs
    GO
    SELECT CAST(title AS char(50)), ytd_sales
    FROM titles
    WHERE type = 'trad_cook'
    GO

    下面是结果集:

    ytd_sales


    Onions, Leeks, and Garlic: Cooking Secrets of the 375
    Fifty Years in Buckingham Palace Kitchens 15096
    Sushi, Anyone? 4095

    (3 row(s) affected)

    E. 使用带有 LIKE 子句的 CAST
    下面的示例将 int 列(ytd_sales 列)转换为 char(20) 列,以便使用 LIKE 子句。

    USE pubs
    GO
    SELECT title, ytd_sales
    FROM titles
    WHERE CAST(ytd_sales AS char(20)) LIKE '15%'
    AND type = 'trad_cook'
    GO

    下面是结果集:

    title ytd_sales


    Fifty Years in Buckingham Palace Kitchens 15096

    本文由新葡亰496net发布于网络数据库,转载请注明出处:数据类型转换,Convert数据类型转换

    关键词:

上一篇:mac使用记录,MySQL加多用户

下一篇:没有了