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

文章目录

• • 
    
• ​​第二十三章 SQL函数 CAST（二）​​
• ​​转换位值​​
• ​​示例​​
  • 
    

第二十三章 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

示例

下面的示例使用​​CAST​​函数将平均值表示为整数，而不是浮点数。

请注意，​​CAST​​将数字截短，而不是四舍五入:

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

下面的例子展示了​​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

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

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

下面的例子展示了​​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

下面的例子展示了​​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

下面的示例展示了使用​​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

下面的示例将日期字段(​​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

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

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

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

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

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

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

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

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

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

时间部分由嵌套的​​CAST​​提供;

日期部分是当前系统日期。