一、概述

本篇文章轉(zhuǎn)載來(lái)著官網(wǎng)在線(xiàn)文檔，文章主要介紹SQL Server數(shù)據(jù)類(lèi)型轉(zhuǎn)換相關(guān)語(yǔ)法、隱式轉(zhuǎn)換、Date樣式等。

?

語(yǔ)法

Syntax for CAST: CAST ( expression AS data_type [ ( length ) ] )Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) 參數(shù)

expression：任何有效的表達(dá)式。

data_type：目標(biāo)數(shù)據(jù)類(lèi)型。這包括 xml、bigint 和 sql_variant。不能使用別名數(shù)據(jù)類(lèi)型。有關(guān)可用數(shù)據(jù)類(lèi)型的詳細(xì)信息，請(qǐng)參閱數(shù)據(jù)類(lèi)型 (Transact-SQL)。length：指定目標(biāo)數(shù)據(jù)類(lèi)型長(zhǎng)度的可選整數(shù)。默認(rèn)值為 30。style：指定 CONVERT 函數(shù)如何轉(zhuǎn)換 expression 的整數(shù)表達(dá)式。如果樣式為 NULL，則返回 NULL。該范圍是由 data_type 確定的。有關(guān)詳細(xì)信息，請(qǐng)參閱“備注”部分。

Date 和 Time 樣式

如果 expression 為 date 或 time 數(shù)據(jù)類(lèi)型，則 style 可以為下表中顯示的值之一。其他值作為 0 進(jìn)行處理。SQL Server 使用科威特算法來(lái)支持阿拉伯樣式的日期格式。 不帶世紀(jì)數(shù)位 (yy) (¹)帶世紀(jì)數(shù)位 (yyyy)標(biāo)準(zhǔn)輸入/輸出 (³)

-	0 或 100 (1,2)	默認(rèn)	mon dd yyyy hh:miAM（或 PM）
1	101	美國(guó)	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	英國(guó)/法國(guó)	dd/mm/yyyy
4	104	德國(guó)	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
-	9 或 109 (1,2)	默認(rèn)設(shè)置 + 毫秒	mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10	110	美國(guó)	mm-dd-yy
11	111	日本	yy/mm/dd
12	112	ISO	yymmdd yyyymmdd
-	13 或 113 (1,2)	歐洲默認(rèn)設(shè)置 + 毫秒	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 或 120 (2)	ODBC 規(guī)范	yyyy-mm-dd hh:mi:ss(24h)
-	21 或 121 (2)	ODBC 規(guī)范（帶毫秒）	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm（無(wú)空格）
-	127(6, 7)	帶時(shí)區(qū) Z 的 ISO8601。	yyyy-mm-ddThh:mi:ss.mmmZ （無(wú)空格）
-	130 (1,2)	回歷 (5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	回歷 (5)	dd/mm/yy hh:mi:ss:mmmAM

1 這些樣式值將返回不確定的結(jié)果。包括所有 (yy)（不帶世紀(jì)數(shù)位）樣式和一部分 (yyyy)（帶世紀(jì)數(shù)位）樣式。

2 默認(rèn)值（style 0 或 100、9 或 109、13 或 113、20 或 120 以及 21 或 121）始終返回世紀(jì)數(shù)位 (yyyy)。

3 轉(zhuǎn)換為 datetime 時(shí)輸入；轉(zhuǎn)換為字符數(shù)據(jù)時(shí)輸出。

4 為用于 XML 而設(shè)計(jì)。對(duì)于從 datetime 或 smalldatetime 到字符數(shù)據(jù)的轉(zhuǎn)換，其輸出格式如上一個(gè)表所述。

5 回歷是有多種變體的日歷系統(tǒng)。SQL Server 使用科威特算法。

注意：默認(rèn)情況下，SQL Server 基于截止年份 2049 年來(lái)解釋兩位數(shù)的年份。換言之，就是將兩位數(shù)的年份 49 解釋為 2049，將兩位數(shù)的年份 50 解釋為 1950。許多客戶(hù)端應(yīng)用程序（如基于自動(dòng)化對(duì)象的應(yīng)用程序）都使用截止年份 2030 年。SQL Server 提供了 two digit year cutoff 配置選項(xiàng)，可通過(guò)此選項(xiàng)更改 SQL Server 使用的截止年份，從而對(duì)日期進(jìn)行一致處理。建議您指定四位數(shù)年份。

6 僅支持從字符數(shù)據(jù)轉(zhuǎn)換為 datetime 或 smalldatetime。僅表示日期或時(shí)間成分的字符數(shù)據(jù)轉(zhuǎn)換為 datetime 或 smalldatetime 數(shù)據(jù)類(lèi)型時(shí)，未指定的時(shí)間成分設(shè)置為 00:00:00.000，未指定的日期成分設(shè)置為 1900-01-01。

7使用可選的時(shí)間區(qū)域指示符 (Z) 更便于將具有時(shí)區(qū)信息的 XML datetime 值映射到?jīng)]有時(shí)區(qū)的 SQL Server datetime 值。Z 是時(shí)區(qū) UTC-0 的指示符。其他時(shí)區(qū)則以 + 或 - 方向的 HH:MM 偏移量來(lái)指示。例如：2006-12-12T23:45:12-08:00。

從 smalldatetime 轉(zhuǎn)換為字符數(shù)據(jù)時(shí)，包含秒或毫秒的樣式將在這些位置上顯示零。使用相應(yīng)的 char 或 varchar 數(shù)據(jù)類(lèi)型長(zhǎng)度從 datetime 或 smalldatetime 值轉(zhuǎn)換時(shí)，可截?cái)嗖恍枰娜掌诓糠帧?/p>

從樣式包含時(shí)間的字符數(shù)據(jù)轉(zhuǎn)換為 datetimeoffset 時(shí)，將在結(jié)果末尾追加時(shí)區(qū)偏移量。

float 和 real 樣式

如果 expression 為 float 或 real，則 style 可以為下表中顯示的值之一。其他值作為 0 進(jìn)行處理。

值輸出

0（默認(rèn)值）	最多包含 6 位。根據(jù)需要使用科學(xué)記數(shù)法。
1	始終為 8 位值。始終使用科學(xué)記數(shù)法。
2	始終為 16 位值。始終使用科學(xué)記數(shù)法。
126, 128, 129	為了保持向后兼容而包括在內(nèi)，在以后的版本中可能不推薦使用。

money 和 smallmoney 樣式

如果 expression 為 money 或 smallmoney，則 style 可以為下表中顯示的值之一。其他值作為 0 進(jìn)行處理。

值輸出

0（默認(rèn)值）	小數(shù)點(diǎn)左側(cè)每三位數(shù)字之間不以逗號(hào)分隔，小數(shù)點(diǎn)右側(cè)取兩位數(shù)，例如 4235.98。
1	小數(shù)點(diǎn)左側(cè)每三位數(shù)字之間以逗號(hào)分隔，小數(shù)點(diǎn)右側(cè)取兩位數(shù)，例如 3,510.92。
2	小數(shù)點(diǎn)左側(cè)每三位數(shù)字之間不以逗號(hào)分隔，小數(shù)點(diǎn)右側(cè)取四位數(shù)，例如 4235.9819。
126	轉(zhuǎn)換為 char(n) 或 varchar(n) 時(shí)，等同于樣式 2

xml 樣式

如果 data_type 為 xml，則 style 可以為下表中顯示的值之一。其他值作為 0 進(jìn)行處理。

值	輸出
0（默認(rèn)值）	使用默認(rèn)的分析行為，即放棄無(wú)用的空格，且不允許使用內(nèi)部 DTD 子集。 注意： 轉(zhuǎn)換為 xml 數(shù)據(jù)類(lèi)型時(shí)，SQL Server 的無(wú)用空格處理方式不同于 XML 1.0。有關(guān)詳細(xì)信息，請(qǐng)參閱生成 XML 實(shí)例。
1	保留無(wú)用空格。此樣式設(shè)置將默認(rèn)的 xml:space 處理方式設(shè)置為與指定了 xml:space="preserve" 的行為相同。
2	啟用有限的內(nèi)部 DTD 子集處理。 如果啟用，則服務(wù)器可使用內(nèi)部 DTD 子集提供的以下信息來(lái)執(zhí)行非驗(yàn)證分析操作。 應(yīng)用屬性的默認(rèn)值。 解析并擴(kuò)展內(nèi)部實(shí)體引用。 檢查 DTD 內(nèi)容模型以實(shí)現(xiàn)語(yǔ)法的正確性。 分析器將忽略外部 DTD 子集。此外，不評(píng)估 XML 聲明來(lái)查看 standalone 屬性是設(shè)置為 yes 還是 no，而是將 XML 實(shí)例當(dāng)成一個(gè)獨(dú)立文檔進(jìn)行分析。
3	保留無(wú)用空格，并啟用有限的內(nèi)部 DTD 子集處理。

二進(jìn)制樣式

如果 expression 為 binary(n)、varbinary(n)、char(n) 或 varchar(n)，則 style 可以為下表中顯示的值之一。表中沒(méi)有列出的樣式值將返回錯(cuò)誤。

值輸出

0（默認(rèn)值）	將 ASCII 字符轉(zhuǎn)換為二進(jìn)制字節(jié)，或者將二進(jìn)制字節(jié)轉(zhuǎn)換為 ASCII 字符。每個(gè)字符或字節(jié)按照 1:1 進(jìn)行轉(zhuǎn)換。 如果 data_type 為二進(jìn)制類(lèi)型，則會(huì)在結(jié)果左側(cè)添加字符 0x。
1, 2	如果 data_type 為二進(jìn)制類(lèi)型，則表達(dá)式必須為字符表達(dá)式。expression 必須由數(shù)量為偶數(shù)的十六進(jìn)制數(shù)字（0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F、a、b、c、d、e、f）組成。如果將 style 設(shè)置為 1，字符 0x 必須為表達(dá)式中的前兩個(gè)字符。如果表達(dá)式中包含的字符數(shù)為奇數(shù)或者包含任何無(wú)效的字符，則會(huì)引發(fā)錯(cuò)誤。 如果轉(zhuǎn)換后的表達(dá)式長(zhǎng)度大于 data_type 長(zhǎng)度，則會(huì)在右側(cè)截?cái)嘟Y(jié)果。 如果固定長(zhǎng)度 data_types 大于轉(zhuǎn)換后的結(jié)果，則會(huì)在結(jié)果右側(cè)添加零。 如果 data_type 為字符類(lèi)型，則表達(dá)式必須為二進(jìn)制表達(dá)式。每個(gè)二進(jìn)制字符均轉(zhuǎn)換為兩個(gè)十六進(jìn)制字符。如果轉(zhuǎn)換后的表達(dá)式長(zhǎng)度大于 data_type 長(zhǎng)度，則會(huì)在右側(cè)截?cái)嘟Y(jié)果。 如果 data_type 為固定大小的字符類(lèi)型，并且轉(zhuǎn)換后的結(jié)果長(zhǎng)度小于其 data_type 長(zhǎng)度，則會(huì)在轉(zhuǎn)換后的表達(dá)式右側(cè)添加空格，以使十六進(jìn)制數(shù)字的個(gè)數(shù)保持為偶數(shù)。 對(duì)于 style 1，將在轉(zhuǎn)換后的結(jié)果左側(cè)添加字符 0x。

隱式轉(zhuǎn)換

隱式轉(zhuǎn)換指那些沒(méi)有指定 CAST 或 CONVERT 函數(shù)的轉(zhuǎn)換。顯式轉(zhuǎn)換指那些需要指定 CAST 或 CONVERT 函數(shù)的轉(zhuǎn)換。以下圖例顯示了可對(duì) SQL Server 系統(tǒng)提供的數(shù)據(jù)類(lèi)型執(zhí)行的所有顯式和隱式數(shù)據(jù)類(lèi)型轉(zhuǎn)換。其中包括 xml、bigint 和 sql_variant。不存在對(duì) sql_variant 數(shù)據(jù)類(lèi)型的賦值進(jìn)行的隱式轉(zhuǎn)換，但是存在轉(zhuǎn)換為 sql_variant 的隱式轉(zhuǎn)換。

在 datetimeoffset 與字符類(lèi)型 char、varchar、nchar 和 nvarchar 之間轉(zhuǎn)換時(shí)，轉(zhuǎn)換后的時(shí)區(qū)偏移量部分的 HH 和 MM 都應(yīng)始終為兩個(gè)數(shù)字，例如 -08:00。

注意：因?yàn)?Unicode 數(shù)據(jù)始終使用偶數(shù)個(gè)字節(jié)，所以在 binary 或 varbinary 與支持 Unicode 的數(shù)據(jù)類(lèi)型之間進(jìn)行轉(zhuǎn)換時(shí)會(huì)使用警告。例如，以下轉(zhuǎn)換不返回十六進(jìn)制值 41；而是返回 4100：SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)。

大值數(shù)據(jù)類(lèi)型

大值數(shù)據(jù)類(lèi)型表現(xiàn)出與小值數(shù)據(jù)類(lèi)型相同的隱式和顯式轉(zhuǎn)換行為，特別是 varchar、nvarchar 和 varbinary 數(shù)據(jù)類(lèi)型。但是，應(yīng)該考慮以下原則：

• 從 image 到 varbinary(max) 的轉(zhuǎn)換與反向轉(zhuǎn)換是隱式轉(zhuǎn)換，text 與 varchar(max)、ntext、nvarchar(max) 之間的轉(zhuǎn)換也是隱式轉(zhuǎn)換。
  
  
• 從大值數(shù)據(jù)類(lèi)型（如 varchar(max)）到小值數(shù)據(jù)類(lèi)型（如 varchar）的轉(zhuǎn)換是隱式轉(zhuǎn)換，但如果大值相對(duì)于指定長(zhǎng)度的小值數(shù)據(jù)類(lèi)型顯得太大，則產(chǎn)生截?cái)唷?br />
  
• 從 varchar、nvarchar 或 varbinary 到其相應(yīng)的大值數(shù)據(jù)類(lèi)型的轉(zhuǎn)換都是隱式執(zhí)行的。
  
  
• 從 sql_variant 數(shù)據(jù)類(lèi)型到大值數(shù)據(jù)類(lèi)型的轉(zhuǎn)換是顯式轉(zhuǎn)換。
  
  
• 大值數(shù)據(jù)類(lèi)型不能轉(zhuǎn)換為 sql_variant 數(shù)據(jù)類(lèi)型。

有關(guān)如何轉(zhuǎn)換 Microsoft .NET Framework 公共語(yǔ)言運(yùn)行時(shí) (CLR) 用戶(hù)定義類(lèi)型的信息，請(qǐng)參閱對(duì)用戶(hù)定義類(lèi)型執(zhí)行操作。有關(guān)如何從 xml 數(shù)據(jù)類(lèi)型進(jìn)行轉(zhuǎn)換的詳細(xì)信息。

xml 數(shù)據(jù)類(lèi)型

當(dāng)您將 xml 數(shù)據(jù)類(lèi)型顯式或隱式轉(zhuǎn)換為字符串或二進(jìn)制數(shù)據(jù)類(lèi)型時(shí)，xml 數(shù)據(jù)類(lèi)型的內(nèi)容將根據(jù)一組規(guī)則進(jìn)行序列化。有關(guān)這些規(guī)則的信息，請(qǐng)參閱 XML 數(shù)據(jù)的序列化。有關(guān)如何從 XML 轉(zhuǎn)換為 CLR 用戶(hù)定義類(lèi)型的信息，請(qǐng)參閱對(duì)用戶(hù)定義類(lèi)型執(zhí)行操作。有關(guān)如何從其他數(shù)據(jù)類(lèi)型轉(zhuǎn)換到 xml 數(shù)據(jù)類(lèi)型的信息。

文本和圖像數(shù)據(jù)類(lèi)型

不支持對(duì) text 和 image 數(shù)據(jù)類(lèi)型進(jìn)行自動(dòng)數(shù)據(jù)類(lèi)型轉(zhuǎn)換。可將 text 數(shù)據(jù)顯式轉(zhuǎn)換為字符數(shù)據(jù)，將 image 數(shù)據(jù)轉(zhuǎn)換為 binary 或 varbinary，但最大長(zhǎng)度是 8000 字節(jié)。如果試圖進(jìn)行不正確的轉(zhuǎn)換，如將包含字母的字符表達(dá)式轉(zhuǎn)換為 int，則 SQL Server 將返回錯(cuò)誤消息。

輸出排序規(guī)則

如果 CAST 或 CONVERT 的輸出是字符串，并且輸入也是字符串，則輸出將與輸入具有相同的排序規(guī)則和排序規(guī)則標(biāo)簽。如果輸入不是字符串，則輸出采用數(shù)據(jù)庫(kù)的默認(rèn)排序規(guī)則以及強(qiáng)制默認(rèn)的排序規(guī)則標(biāo)簽。

若要為輸出分配不同的排序規(guī)則，請(qǐng)將 COLLATE 子句應(yīng)用于 CAST 或 CONVERT 函數(shù)的結(jié)果表達(dá)式。例如：

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

截?cái)嘟Y(jié)果和舍入結(jié)果

將字符或二進(jìn)制表達(dá)式（char、nchar、nvarchar、varchar、binary 或 varbinary）轉(zhuǎn)換為其他數(shù)據(jù)類(lèi)型的表達(dá)式時(shí)，可截?cái)鄶?shù)據(jù)，僅顯示部分?jǐn)?shù)據(jù)，或返回錯(cuò)誤（因?yàn)榻Y(jié)果太短而無(wú)法顯示）。除了下表顯示的轉(zhuǎn)換，其他到 char、varchar、nchar、nvarchar、binary 和 varbinary 的轉(zhuǎn)換都將被截?cái)唷?/p>

?

被轉(zhuǎn)換的數(shù)據(jù)類(lèi)型轉(zhuǎn)換為的數(shù)據(jù)類(lèi)型結(jié)果

int、smallint 或 tinyint	char	*
?	varchar	*
?	nchar	E
?	nvarchar	E
money、smallmoney、numeric、decimal、float 或 real	char	E
?	varchar	E
?	nchar	E
?	nvarchar	E

* = 結(jié)果長(zhǎng)度太短而無(wú)法顯示。E = 因?yàn)榻Y(jié)果長(zhǎng)度太短無(wú)法顯示而返回錯(cuò)誤。

SQL Server 僅保證往返轉(zhuǎn)換（即從原始數(shù)據(jù)類(lèi)型進(jìn)行轉(zhuǎn)換后又返回原始數(shù)據(jù)類(lèi)型的轉(zhuǎn)換）在各版本間產(chǎn)生相同值。以下示例顯示的即是這樣的往返轉(zhuǎn)換：

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))

注意：不要嘗試構(gòu)造 binary 值然后將其轉(zhuǎn)換為數(shù)值數(shù)據(jù)類(lèi)型類(lèi)別的一種數(shù)據(jù)類(lèi)型。SQL Server 不能保證 decimal 或 numeric 數(shù)據(jù)類(lèi)型到 binary 的轉(zhuǎn)換結(jié)果在 SQL Server 的各個(gè)版本中都相同。

轉(zhuǎn)換小數(shù)位數(shù)不同的數(shù)據(jù)類(lèi)型時(shí)，結(jié)果值有時(shí)被截?cái)?#xff0c;有時(shí)被舍入。下表顯示了此行為。

被轉(zhuǎn)換的數(shù)據(jù)類(lèi)型轉(zhuǎn)換到的數(shù)據(jù)類(lèi)型行為

numeric	numeric	舍入
numeric	int	截?cái)?/p>
numeric	money	舍入
money	int	舍入
money	numeric	舍入
float	int	截?cái)?/p>
float	numeric	舍入
float	datetime	舍入
datetime	int	舍入

例如，以下轉(zhuǎn)換的結(jié)果為 10：

SELECT CAST(10.6496 AS int)

在進(jìn)行數(shù)據(jù)類(lèi)型轉(zhuǎn)換時(shí)，若目標(biāo)數(shù)據(jù)類(lèi)型的小數(shù)位數(shù)小于源數(shù)據(jù)類(lèi)型的小數(shù)位數(shù)，則該值將被截?cái)唷＠?#xff0c;以下轉(zhuǎn)換的結(jié)果為 $10.3497：

SELECT CAST(10.3496847 AS money)

當(dāng)非數(shù)字型 char、nchar、varchar 或 nvarchar 數(shù)據(jù)轉(zhuǎn)換為 int、float、numeric 或 decimal 時(shí)，SQL Server 將返回錯(cuò)誤消息。當(dāng)空字符串 (" ") 轉(zhuǎn)換為 numeric 或 decimal 時(shí)，SQL Server 也返回錯(cuò)誤。

?

?

備注：文章轉(zhuǎn)載來(lái)自官方在線(xiàn)文檔。

?

?

備注： ??? 作者：pursuer.chen ??? 博客：http://www.cnblogs.com/chenmh 本站點(diǎn)所有隨筆都是原創(chuàng)，歡迎大家轉(zhuǎn)載；但轉(zhuǎn)載時(shí)必須注明文章來(lái)源，且在文章開(kāi)頭明顯處給明鏈接，否則保留追究責(zé)任的權(quán)利。 《歡迎交流討論》

?

總結(jié)

以上是生活随笔為你收集整理的SQL Server Cast、Convert数据类型转换的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。