第二十三章 SQL函数 CAST（二）

转换位值

可以将expr值转换为BIT以返回0或1。
如果expr为1或任何其他非零数值，则返回1。
如果expr为“TRUE”、“TRUE”或“TRUE”，则返回1。
(“True”可以用任何大小写组合表示，但不能缩写为“T”。)
如果expr是任何其他非数值值，则返回0。
如果expr为0，则返回0。

在下面的例子中，前5个CAST操作返回1，后5个CAST操作返回0:

SELECT CAST(1 AS BIT) AS One, 
       CAST(7 AS BIT) AS Num,      
       CAST(743.6 AS BIT) AS Frac,  
       CAST(0.3 AS BIT) AS Zerofrac,
       CAST('tRuE' AS BIT) AS TrueWord,
       CAST(0 AS BIT) AS Zero,  
       CAST('FALSE' AS BIT) AS FalseWord, 
       CAST('T' AS BIT) AS T,    
       CAST('F' AS BIT) AS F,   
       CAST(0.0 AS BIT) AS Zerodot     

image.png

示例

下面的示例使用CAST函数将平均值表示为整数，而不是浮点数。
请注意，CAST将数字截短，而不是四舍五入:

SELECT DISTINCT AVG(Age) AS AvgAge,
   CAST(AVG(Age) AS INTEGER) AS IntAvgAge
      FROM Sample.Person

image.png

下面的例子展示了CAST函数如何将pi(一个浮点数)转换为不同的数值数据类型:

SELECT 
   CAST({fn PI()} As INTEGER) As IntegerPi,
   CAST({fn PI()} As SMALLINT) As SmallIntPi,
   CAST({fn PI()} As DECIMAL) As DecimalPi,
   CAST({fn PI()} As NUMERIC) As NumericPi,
   CAST({fn PI()} As DOUBLE) As DoublePi

image.png

注意，在下面的例子中，精度和比例值被解析，但不会改变CAST返回的值:

SELECT 
   CAST({fn PI()} As DECIMAL) As DecimalPi,
   CAST({fn PI()} As DECIMAL(6,3)) As DecimalPSPi

image.png

下面的例子展示了CAST函数如何将pi(浮点数)转换为不同的字符数据类型:

SELECT 
   CAST({fn PI()} As CHAR) As CharPi,
   CAST({fn PI()} As CHAR(4)) As CharNPi,
   CAST({fn PI()} As CHAR VARYING) As CharVaryingPi,
   CAST({fn PI()} As VARCHAR(4)) As VarCharNPi

image.png

下面的例子展示了CAST函数如何将Name(一个字符串)转换为不同的字符数据类型:

SELECT DISTINCT 
   CAST(Name As CHAR) As CharName,
   CAST(Name As CHAR(4)) As CharNName,
   CAST(Name As CHAR VARYING) As CharVaryingName,
   CAST(Name As VARCHAR(4)) As VarCharNName
      FROM Sample.Person

image.png

下面的示例展示了使用CAST函数将Name(字符串)转换为不同的数字数据类型时会发生什么。
在任何情况下，返回值都是0 (0):

SELECT DISTINCT 
   CAST(Name As INT) As IntName,
   CAST(Name As SMALLINT) As SmallIntName,
   CAST(Name As DEC) As DecName,
   CAST(Name As NUMERIC) As NumericName
   FROM Sample.Person

image.png

下面的示例将日期字段(DOB)转换为数字数据类型和几个字符数据类型。
将日期转换为数字将返回等效的$HOROLOG整数。
将日期转换为字符数据类型将返回输入格式的日期字符串(CHAR VARYING或character VARYING)或ODBC日期字符串格式的日期(部分或全部):

SELECT DISTINCT DOB,
   CAST(DOB As INT) AS IntDate,
   CAST(DOB As CHAR) AS CharDate,
   CAST(DOB As CHAR(6)) AS CharNDate,
   CAST(DOB As CHAR VARYING) AS CharVaryDate,
   CAST(DOB As VARCHAR(10)) AS VarCharNDate
      FROM Sample.Person

image.png

下面的示例将字符串转换为DATE和TIME数据类型:

SELECT CAST('1936-11-26' As DATE) AS StringToDate,
       CAST('14:33:45.78' AS TIME) AS StringToTime

image.png

日期只能转换为YYYY-MM-DD格式的字符串。
其他格式的字符串返回0。
请注意，在将字符串转换为TIME数据类型时，小数秒被截断(而不是四舍五入)。

下面的例子将日期转换为TIMESTAMP数据类型:

SELECT DISTINCT DOB,
   CAST(DOB As TIMESTAMP) AS DateToTstamp
      FROM Sample.Person

image.png

生成的时间戳格式为“YYYY-MM-DD hh:mm:ss”。

下面的示例将字符串转换为TIME数据类型，然后将结果时间转换为TIMESTAMP数据类型:

SELECT CAST(CAST('14:33:45.78' AS TIME) As TIMESTAMP) AS TimeToTstamp

image.png

生成的时间戳格式为“YYYY-MM-DD hh:mm:ss”。
时间部分由嵌套的CAST提供;
日期部分是当前系统日期。