第二十二章 SQL函数 CAST（一）_yaoxin521123_cast sql

大大的周 02-07 1117

文章目录第二十二章 SQL函数 CAST（一）大纲参数描述转换数字字符串类型转换转换为DATE、TIME和TIMESTAMP转换NULL和空字符串转换日期

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

将给定表达式转换为指定数据类型的函数。

SQL CAST函数将表达式的数据类型转换为指定的数据类型。当Expr的数据类型是标准数据类型或标准数据类型(如%Library.String、%Library.Time、%Library.Date或%Library.TimeStamp)的子类时，CAST可以转换该数据类型。

可以将expr转换为以下任何数据类型

CHAR或CHARACTER:用其初始字符表示数字或字符串。当指定为CAST或CONVERT时，不带n的VARCHAR默认长度为30个字符。否则，VARCHAR数据类型(没有指定大小)将被映射到一个1个字符的MAXLEN，如data Types表所示。 NCHAR等价于CHAR; NVARCHAR相当于VARCHAR。CHAR(n)、CHARACTER(n)或VARCHAR(n):用n指定的字符数表示数字或字符串。CHAR VARYING或CHARACTER VARYING:用原值中的字符数表示数字或字符串。INT、INTEGER、BIGINT、SMALLINT和TINYINT:用整数部分表示数字。十进制数字被截断。“DEC”、“DECIMAL”和“NUMERIC”:用原值中的位数来表示数字。使用 $DECIMAL函数进行转换，该函数将$DOUBLE值转换为$DECIMAL值。如果指定了p(精度)，则保留为定义的数据类型的一部分，但不会影响CAST返回的值。如果指定正整数的s (scale)值，则十进制值四舍五入为指定的位数。 (在Display模式中包含适当数量的尾随零，但在Logical模式和ODBC模式中被截断。) 如果指定s=0，数值将四舍五入为整数。如果指定s=-1，数值将被截断为整数。DOUBLE表示IEEE浮点标准。MONEY和SMALLMONEY是货币数字数据类型。货币数据类型的规模总是4。DATE:日期。日期可以用以下任何一种格式表示，这取决于上下文:所在地区的显示日期格式(例如，MM/DD/YYYY); ODBC日期格式(YYYY-MM-DD); 或$HOROLOG整数日期存储格式(nnnnn)。必须将$HOROLOG日期部分值指定为整数，而不是数字字符串。TIME:时间。时间可以用以下任何一种格式表示，这取决于上下文:本地地区的显示时间格式(例如，hh:mm:ss); ODBC日期格式(hh:mm:ss); 或$HOROLOG整数时间存储格式(nnnnn)。必须将$HOROLOG时间部分值指定为整数，而不是数字字符串。POSIXTIME:以64位有符号整数的形式表示日期和时间戳。TIMESTAMP、DATETIME和SMALLDATETIME:日期和时间戳，格式为YYYY-MM-DD hh:mm:ss.nnn。这对应于ObjectScript $ZTIMESTAMP特殊变量。BIT表示单个二进制值。BINARY、BINARY VARING和VARBINARY表示数据类型%Library.Binary(xDBC数据类型BINARY)的值。对于BINARY，可选的n长度缺省为1，对于BINARY VARING和VARBINARY，缺省为30。转换为二进制值时，实际上不会执行数据转换。不会截断指定长度为n的值的长度。GUID表示数据类型%Library.UniqueIdentifier的36个字符值。如果提供的expr超过36个字符，CAST将返回expr的前36个字符。要生成GUID值，请使用%SYSTEM.Util.CreateGUID()方法。转换数字

数值可以转换为数字数据类型或字符数据类型。

当将数值结果转换为缩略值时，数值将被截断，而不是四舍五入。例如，将98.765转换为INT返回98，转换为CHAR返回9，转换为CHAR(4)返回98.7。请注意，将负数转换为CHAR仅返回负号，将小数转换为CHAR仅返回小数点。

数字可以由数字0到9、小数点、一个或多个前导符号(+或-)、指数符号(E或E)后面最多跟一个+或-符号组成。数字不能包含组分隔符(逗号)。

在执行强制转换之前 SQL将数字解析为其规范形式:执行指数运算。 IRIS带前导和后导零、前导加号和后导小数点。在转换数字之前解析多个符号。然而，SQL将双负号作为注释指示符; 遇到数字中的双负号时，会将该行代码的其余部分作为注释处理。

浮点数可以采用DEC、DECIMAL或NUMERIC数据类型。 DOUBLE数据类型根据IEEE浮点标准表示浮点数。浮点数据类型比DOUBLE数据类型具有更高的精度，适合于大多数应用程序。不能使用CAST将浮点数转换为DOUBLE数据类型; 相反，使用ObjectScript $DOUBLE函数。

当数字值被转换为日期或时间数据类型时，它在SQL中显示为0 (0); 但是，当将数字转换为日期或时间时，从嵌入式SQL传递到ObjectScript，它显示为相应的$HOROLOG值。

字符串类型转换

可以将字符串强制转换为另一种字符数据类型，返回单个字符、前n个字符或整个字符串。

在执行强制转换之前，SQL会解析内嵌的引号字符('can' t'=can't)和字符串连接('can'||'not'=cannot)。保留前导和尾随空格。

当字符串被转换为数字类型时，它总是返回一个数字零(0)。

转换为DATE、TIME和TIMESTAMP

可以将字符串强制转换为DATE、TIME或TIMESTAMP数据类型。通过以下操作，可以得到一个有效的值:

DATE:格式为“yyyy-mm-dd”的字符串可以转换为DATE。此字符串格式对应于ODBC日期格式。执行值和范围检查。年份必须在0001到9999之间(包括)，从01月到12月，以及该月的适当日期(例如，02/29只在闰年有效)。无效日期，如2013-02-29返回1840-12-31(逻辑日期0)。

将添加月和日字段中缺少的前导零。此转换的显示方式取决于显示模式和区域设置的日期显示格式。例如，‘2004-11-23’可能显示为‘11/23/2004’。在嵌入式SQL中，此强制转换作为相应的$HOROLOG日期整数返回。无效的ODBC日期或非数字字符串在转换为日期时在逻辑模式下表示为0；日期0显示为1840-12-31。

TIME:格式为'hh:mm'、'hh:mm:ss'或'hh:mm:ss'的字符串。 nn'(有任意n个小数秒位数)可以被转换为TIME。此字符串格式对应于ODBC时间格式。执行值和范围检查。缺少前导零被添加。在嵌入式SQL中，这种转换将作为相应的$HOROLOG时间整数返回。当转换为time时，无效的ODBC时间或非数字字符串在逻辑模式中表示为0; 时间0显示为00:00:00。

TIMESTAMP:由有效日期和时间、有效日期或有效时间组成的字符串可以转换为TIMESTAMP。日期部分可以采用多种格式。缺少的日期部分默认为1841-01-01。缺少的时间段默认为00:00:00。缺少前导零将添加到月和日。小数秒(如果指定了)可以在句点(.)或冒号(:)之前加上。这些符号有不同的含义。句号表示标准分数; 因此，12:00:00.4表示十分之四秒，而12:00:00.004表示千分之四秒。冒号表示接下来的是千分之一秒; 因此12:00:00:4表示千分之四秒。冒号后面的数字限制为3位。

转换NULL和空字符串

NULL可以转换为任何数据类型并返回NULL。

空字符串(")的类型转换如下:

所有字符数据类型返回NULL。所有数字数据类型都返回0(零)，以及尾随分数零的适当数量。 DOUBLE数据类型返回零，不带尾随分数零。DATE数据类型返回12/31/1840。TIME数据类型返回00:00:00。TIMESTAMP、DATETIME和SMALLDATETIME数据类型返回NULL。BIT数据类型返回0。所有二进制数据类型都返回NULL。转换日期

可以将日期转换为日期数据类型、数字数据类型或字符数据类型。

将日期转换为POSIXTIME数据类型会将时间戳转换为编码的64位带符号整数。由于日期没有时间部分，时间部分被提供给时间戳编码为00:00:00。 CAST执行日期验证; 如果expr值不是有效日期，则会发出SQLCODE -400错误。

将日期转换为TIMESTAMP、DATETIME或SMALLDATETIME数据类型将返回一个格式为YYYY-MM-DD hh:mm:ss的时间戳。由于日期没有时间部分，因此生成的时间戳的时间部分总是00:00:00。 CAST执行日期验证; 如果expr值不是有效日期，则会发出SQLCODE -400错误。

下面的动态SQL示例将DATE数据类型字段转换为TIMESTAMP和POSIXTIME:

ClassMethod Cast() { s myquery = 2 s myquery(1) = "SELECT TOP 5 DOB,CAST(DOB AS TIMESTAMP) AS TStamp," s myquery(2) = "CAST(DOB AS POSIXTIME) AS Posix FROM Sample.Person" s tStatement = ##class(%SQL.Statement).%New() s qStatus = tStatement.%Prepare(.myquery) if qStatus '= 1 { w "%Prepare failed:" d $System.Status.DisplayError(qStatus) q } s rset = tStatement.%Execute() d rset.%Display() w !,"End of data" } d ##class(PHA.Test).Cast() DOB TStamp Posix 1976-03-01 1976-03-01 00:00:00 1976-03-01 00:00:00 2008-07-25 2008-07-25 00:00:00 2008-07-25 00:00:00 1976-03-30 1976-03-30 00:00:00 1976-03-30 00:00:00 1961-07-29 1961-07-29 00:00:00 1961-07-29 00:00:00 1924-03-10 1924-03-10 00:00:00 1924-03-10 00:00:00 5 Rows(s) Affected End of data

注:IRIS以下不可用POSIXTIME。

下面的动态SQL示例将TIMESTAMP数据类型字段转换为DATE和POSIXTIME:

ClassMethod Cast1() { s myquery = 2 s myquery(1) = "SELECT TOP 5 EventDate,CAST(EventDate AS DATE) AS Horolog," s myquery(2) = "CAST(EventDate AS POSIXTIME) AS Posix FROM Aviation.Event" s tStatement = ##class(%SQL.Statement).%New() s qStatus = tStatement.%Prepare(.myquery) if qStatus '= 1 { w "%Prepare failed:" d $System.Status.DisplayError(qStatus) q } s rset = tStatement.%Execute() d rset.%Display() w !,"End of data" }

将日期转换为数字数据类型将返回该日期的$HOROLOG值。这是一个整数值，表示自1840年12月31日以来的天数。

将日期转换为字符数据类型可以返回完整的日期，也可以返回数据类型长度所允许的全部日期。但是，对所有字符数据类型来说，显示格式并不相同。 CHAR VARYING和CHARACTER VARYING数据类型以显示格式返回完整的日期。例如，如果日期显示为MM/DD/YYYY，这些数据类型将日期返回为具有相同格式的字符串。其他字符数据类型以ODBC日期格式的字符串形式返回日期(或其中的一部分)。例如，如果日期显示为mm/dd/yyyy，这些数据类型将日期返回为yyyy - mm - dd格式的字符串。因此，对于日期04/24/2004,CHAR数据类型返回'2'(年份的第一个字符)，而CHAR(8)返回' 2004-04 - '。