第155章 SQL函数 UPPER

将字符串表达式中的所有小写字母转换为大写字母的大小写转换函数。

大纲

UPPER(expression)

UPPER expression

参数

• expression - 字符串表达式，可以是列名、字符串文字或另一个函数的结果，其中基础数据类型可以表示为任何字符类型（例如 CHAR 或 VARCHAR）。

描述

UPPER 函数将所有字母字符转换为大写字母。这是 LOWER 函数的反函数。 UPPER 保留不变的数字、标点符号和前导或尾随空格。

UPPER 不强制将数字解释为字符串。 SQL 从数字中删除前导零和尾随零。指定为字符串的数字保留前导零和尾随零。

也可以使用 UPPER() 方法调用从 ObjectScript 调用此函数：

$SYSTEM.SQL.Functions.UPPER(expression)

UPPER 是字母大小写转换的标准函数，而不是排序规则。对于大写排序规则，使用 %SQLUPPER，它提供了数字、NULL 值和空字符串的高级排序规则。

示例

以下示例返回所有名称，选择那些名称的大写形式以“JO”开头的名称：

SELECT Name
FROM Sample.Person
WHERE UPPER(Name) %STARTSWITH UPPER('JO')

以下示例以大写形式返回所有名称，选择名称以“JO”开头的名称：

SELECT UPPER(Name) AS CapName
FROM Sample.Person
WHERE Name %STARTSWITH UPPER('JO')

以下嵌入式 SQL 示例将小写的希腊字母 Delta 转换为大写。此示例使用 UPPER 语法，该语法使用空格而不是括号来分隔关键字和参数：

/// d ##class(PHA.TEST.SQLFunction).Upper()
ClassMethod Upper()
{
    &sql(
    	SELECT 
    		UPPER {fn CHAR(948)},{fn CHAR(948)}
    	INTO 
    		:a,:b
    	FROM 
    		Sample.Person
    )
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,a,!,b 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Upper()
 
Δ
δ

第154章 SQL函数 UNIX_TIMESTAMP

注：此函数IRIS可用，Cache不可用。

将日期表达式转换为 UNIX 时间戳的日期/时间函数。

大纲

UNIX_TIMESTAMP([date-expression])

参数

• date-expression - 可选 — 作为列名称、另一个标量函数的结果或日期或时间戳文字的表达式。 UNIX_TIMESTAMP 不会从一个时区转换到另一个时区。如果省略 date-expression，则默认为当前 UTC 时间戳。

描述

UNIX_TIMESTAMP 返回一个 UNIX® 时间戳，即自 '1970-01-01 00:00:00'以来的秒数（和小数秒）。

如果不指定 date-expression，则 date-expression 默认为当前 UTC 时间戳。因此，假设系统范围的默认精度为 3，UNIX_TIMESTAMP() 等效于 UNIX_TIMESTAMP(GETUTCDATE(3))。

如果指定 date-expression，UNIX_TIMESTAMP 会将指定的 date-expression 值转换为 UNIX 时间戳，计算该时间戳的秒数。 UNIX_TIMESTAMP 可以返回正数或负数秒数。

UNIX_TIMESTAMP 将其值作为数据类型 %Library.Numeric 返回。它可以返回小数秒的精度。如果不指定 date-expression，它将采用当前配置的系统范围精度。如果指定 date-expression，它将从 date-expression 获取其精度。

日期表达式值

可选的日期表达式可以指定为：

• ODBC 时间戳值（数据类型 %Library.TimeStamp）：YYYY-MM-DD HH:MI:SS.FFF
• PosixTime 时间戳值（数据类型 %Library.PosixTime）：编码的 64 位有符号整数。
• $HOROLOG 日期值（数据类型 %Library.Date）：自 1840 年 12 月 31 日以来的天数，其中第 1 天是 1841 年 1 月 1 日。
• $HOROLOG 时间戳，带或不带小数秒：64412,54736。

UNIX_TIMESTAMP 不进行时区转换：如果 date-expression 为 UTC 时间，则返回 UTC UnixTime；如果 date-expression 是本地时间，则返回本地 UnixTime 值。

小数秒精度

小数秒总是被截断而不是四舍五入到指定的精度。

• %Library.TimeStamp 数据类型格式的日期表达式的最大精度为 9。支持的实际位数由日期表达式精度参数、配置的默认时间精度和系统功能确定。如果指定的精度大于配置的默认时间精度，则精度的附加数字将作为尾随零返回。
• %Library.PosixTime 数据类型格式的日期表达式的最大精度为六。每个 POSIXTIME 值都是使用六位精度计算的；除非提供，否则这些小数位默认为零。

配置精度

可以使用以下配置默认精度：

• 带有 TIME_PRECISION 选项的 SET OPTION。
• 系统范围的 $SYSTEM.SQL.Util.SetOption() 方法配置选项 DefaultTimePrecision。要确定当前设置，请调用显示默认时间精度的 $SYSTEM.SQL.CurrentSettings()；默认值为 0。
• 转到管理门户，选择系统管理、配置、SQL 和对象设置、SQL。查看和编辑 GETDATE()、CURRENT_TIME 和 CURRENT_TIMESTAMP 的默认时间精度的当前设置。

指定一个 0 到 9（含）整数作为要返回的默认精度的小数位数。默认值为 0。返回的实际精度取决于平台；超过系统上可用精度的精度数字将返回为零。

日期和时间函数比较

UNIX_TIMESTAMP 返回日期和时间，表示为从任意日期经过的秒数。

TO_POSIXTIME 返回一个编码的 64 位有符号（一个 %PosixTime 时间戳），它是根据 UNIX 时间戳计算的。

GETUTCDATE 以 %TimeStamp（ODBC 时间戳）数据类型或 %PosixTime（编码的 64 位带符号整数）数据类型值的形式返回通用（独立于时区）日期和时间。 %PosixTime 值是根据相应的 UNIX 时间戳值计算得出的。 %PosixTime 编码有助于快速的时间戳比较和计算。 %Library.PosixTime 类提供 UnixTimeToLogical() 方法将 UNIX 时间戳转换为 PosixTime 时间戳，并提供 LogicalToUnixTime() 方法将 PosixTime 时间戳转换为 UNIX 时间戳。这些方法都不执行时区转换。

还可以使用 ObjectScript $ZTIMESTAMP 特殊变量返回通用（时区无关）时间戳。

ObjectScript $ZDATETIME 函数 dformat -2 采用 $HOROLOG 日期并返回 UNIX 时间戳； $ZDATETIMEH dformat -2 采用 UNIX 时间戳并返回 %HOROLOG日期。这些 ObjectScript 函数将本地时间转换为 UTC 时间。 UNIX_TIMESTAMP 不会将本地时间转换为 UTC 时间。

示例

以下示例返回 UTC UNIX 时间戳。第一个选择项采用日期表达式默认值，第二个指定显式 UTC 时间戳：

SELECT 
   UNIX_TIMESTAMP() AS DefaultUTC,
   UNIX_TIMESTAMP(GETUTCDATE(3)) AS ExplicitUTC
   
   
1652164931	1652164931

以下示例返回当前本地日期和时间的本地 UNIX 时间戳，以及 UTC 日期和时间值的 UTCUNIX 时间戳。第一个选择项指定本地 CURRENT_TIMESTAMP，第二个指定 $HOROLOG（本地日期和时间），第三个指定当前 UTC 日期和时间：

SELECT 
   UNIX_TIMESTAMP(CURRENT_TIMESTAMP(2)) AS CurrTSLocal,
   UNIX_TIMESTAMP($HOROLOG) AS HorologLocal,
   UNIX_TIMESTAMP(GETUTCDATE(3)) AS ExplicitUTC
   
   
1652193775	1652193775	1652164975

以下示例比较了 UNIX_TIMESTAMP（不转换本地时间）和 $ZDATETIME（转换本地时间）：

/// d ##class(PHA.TEST.SQLFunction).UCase()
ClassMethod UCase()
{
	s unixutc = $ZDATETIME($HOROLOG,-2)
	s myquery = "SELECT UNIX_TIMESTAMP($HOROLOG) AS UnixLocal,? AS UnixUTC"
	s tStatement = ##class(%SQL.Statement).%New()
	s qStatus = tStatement.%Prepare(myquery)
	s rset = tStatement.%Execute(unixutc)
	d rset.%Display()
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).UCase()
UnixLocal	UnixUTC
1652193839	1652165039

1 Rows(s) Affected

第153章 SQL函数 UCASE

将字符串中的所有小写字母转换为大写字母的大小写转换函数。

大纲

UCASE(string-expression)

{fn UCASE(string-expression)}

参数

• string-expression - 要将其字符转换为大写的字符串。表达式可以是列名、字符串文字或另一个标量函数的结果，其中基础数据类型可以表示为任何字符类型（例如 CHAR 或 VARCHAR）。

描述

UCASE 将小写字母转换为大写以进行显示。它对非字母字符没有影响；它保留不变的数字、标点符号和前导或尾随空格。

请注意，UCASE 可用作 ODBC 标量函数（使用花括号语法）或 SQL 通用函数。

UCASE 不强制将数字解释为字符串。 SQL 从数字中删除前导零和尾随零。指定为字符串的数字保留前导零和尾随零。

UCASE 不影响排序规则。 %SQLUPPER 函数是 SQL 中为不区分大小写的排序规则转换数据值的首选方法。

也可以使用 UPPER() 方法调用从 ObjectScript 调用此函数：

$SYSTEM.SQL.UPPER(expression)

示例

以下示例以大写字母返回每个人的姓名：

SELECT Name,{fn UCASE(Name)} AS CapName
     FROM Sample.Person

UCASE 也适用于 Unicode（非 ASCII）字母字符，如下面的嵌入式 SQL 示例所示，它将希腊字母从小写转换为大写：

/// d ##class(PHA.TEST.SQLFunction).UCase()
ClassMethod UCase()
{
	s a=$CHAR(950,949,965,963)
	&sql(
		SELECT 
			UCASE(:a)
		INTO 
			:b
		FROM 
			Sample.Person
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,a,!,b 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).UCase()
 
ζευσ
ΖΕΥΣ

第152章 SQL函数 $TSQL_NEWID

大纲

$TSQL_NEWID()

描述

$TSQL_NEWID 返回一个全局唯一 ID (GUID)。 GUID 用于在偶尔连接的系统上同步数据库。 GUID 是一个 36 个字符的字符串，由 32 个十六进制数字组成，由连字符分成五组。它的数据类型是 %Library.UniqueIdentifier。

在 SQL 中提供了 $TSQL_NEWID 以支持Transact-SQL (TSQL)。对应的 TSQL 函数是 NEWID。

$TSQL_NEWID 函数不接受任何参数。请注意，参数括号是必需的。

%Library.GUID 抽象类提供对全局唯一 ID 的支持，包括 AssignGUID() 方法，该方法可用于为类分配全局唯一 ID。要生成 GUID 值，请使用 %SYSTEM.Util.CreateGUID() 方法。

示例

以下示例返回一个 GUID：

SELECT $TSQL_NEWID()

E8B3E0AB-5F5B-4E7F-B59F-166F5A56E055

在使用xDBC连接到字符集为US7ASCII的Oracle数据库时，大家可能遇到过中文的乱码问题，尤其是使用Oracle自己的xDBC驱动的时候。

字符集为US7ASCII的Oracle数据库虽然可以保存中文数据，但给客户端带来了很多麻烦，需要对获取和提交的数据进行转码。

在Ensemble/Health Connect/InterSystems IRIS 中使用SQL适配器连接到这样的Oracle数据库时，可以使用$ZCVT函数进行转码。

1. $ZCVT函数

$ZCVT函数是广泛使用的字符串转换函数，可以做大小写转换、编码转换、URL 和 URI 转换等。我们用其编码转换能力来解决字符集转码问题。

2. 获取的SQL结果集数据有中文时

这时，Oracle的驱动返回的中文数据通常是GB码，而不是Unicode或UTF码。可以通过$ZCVT函数对GB码的数据进行转码，转换为Unicode：

Set tCorrectData = $ZCVT(tOriginalData,"I","GB18030")

其中$ZCVT函数的第一个参数tOriginalData是获取到到结果集字段值；第二个参数“I”说明tOriginalData是输入字符串；第三个参数“GB18030”是说明输入字符串的字符集编码是GB18030。

[toc]

第151章 SQL函数 %TRUNCATE

将字符串截断为指定长度并应用 EXACT 排序规则的排序规则函数。

大纲

%TRUNCATE(expression[,length])

参数

• expression - 字符串表达式，可以是列名、字符串文字或另一个函数的结果，其中基础数据类型可以表示为任何字符类型（例如 CHAR 或 VARCHAR2）。表达式可以是子查询。
• length - 可选 — 截断长度，指定为整数。返回表达式的初始长度字符。如果省略长度，则 %TRUNCATE 排序规则与 %EXACT 排序规则相同。可以用双括号将长度括起来以抑制文字替换：((length))。

描述

%TRUNCATE 将表达式截断为指定的长度，然后在 EXACT 排序规则序列中返回它。

EXACT 排序规则首先按数字顺序排列纯数值（x=+x 的值），然后是字符串排序规则序列中的所有其他字符。 EXACT 字符串整理顺序与 ANSI 标准 ASCII 整理顺序相同：在大写字母字符之前整理数字，在小写字母字符之前整理大写字母字符。标点符号出现在序列中的多个位置。

%TRUNCATE 通过 NULL 不变。

%TRUNCATE 是一个 SQL 扩展，用于 SQL 查找查询。

也可以使用 TRUNCATE() 方法调用从 ObjectScript 调用此函数：

DHC-APP>  WRITE $SYSTEM.SQL.TRUNCATE("This long string",9)
This long

示例

以下示例使用 %TRUNCATE 返回 Name 值的前四个字符：

SELECT TOP 5 Name,%TRUNCATE(Name,4) AS ShortName
FROM Sample.Person

以下示例将 %TRUNCATE 应用于子查询：

SELECT TOP 5 Name, %TRUNCATE((SELECT Name FROM Sample.Company),10) AS Company
FROM Sample.Person

以下示例在 GROUP BY 子句中使用 %TRUNCATE 创建一个字母列表，该列表返回以每个字母开头的名称数量：

SELECT Name AS FirstLetter,COUNT(Name) AS NameCount 
FROM Sample.Person GROUP BY %TRUNCATE(Name,1) ORDER BY Name 

以下两个示例显示 %TRUNCATE 如何执行 EXACT 排序规则。第一个示例中的 ORDER BY 将 Home_Street 截断为两个字符。因为街道地址的前两个字符几乎总是数字，所以 Home_Street 字段按其前两个数字的数字顺序排列。

SELECT Name,Home_Street
FROM Sample.Person
ORDER BY %TRUNCATE(Home_Street,2)

第二个示例中的 ORDER BY 将 Home_Street 截断为四个字符。因为某些街道地址的第四个字符不是数字（例如空格），所以以四个（或更多）数字开头的 Home_Street 值按数字顺序排列，然后是包含非数字的 Home_Street 值前四个字符中的字符按字符串顺序排列：

SELECT Name,Home_Street
FROM Sample.Person
ORDER BY %TRUNCATE(Home_Street,4)

第150章 SQL函数 TRUNCATE

标量数值函数，按指定位数截断一个数。

大纲

{fn TRUNCATE(numeric-expr,scale)}

参数

• numeric-expr - 要截断的数字。数字或数字表达式。
• scale - 计算结果为一个整数的表达式，该整数指定要截断的位数，从小数点开始计算。可以是零、正整数或负整数。如果比例是小数，会将其舍入为最接近的整数。

Truncate返回NUMERIC或DOUBLE数据类型。如果NUMERIC-EXPR的数据类型为DOUBLE，则TRUNCATE返回DOUBLE；否则返回NUMERIC。

描述

TRUNCATE通过从小数点开始按小数位数截断NUMERIC-EXPR。它不对数字进行四舍五入，也不添加填充零。在截断操作之前，将删除前导零和尾随零。

• 如果小数位数为正数，则在小数点右侧的位数处执行截断。如果小数位数等于或大于小数位数，则不会发生截断或零填充。
• 如果Scale为零，则该数字将被截断为整数。换句话说，在小数点右侧的零位数处执行截断；所有小数位和小数点本身都被截断。
• 如果小数位数为负数，则在小数点左侧的位数处执行截断。如果小数位数等于或大于数字中的整数位数，则返回零。
• 如果NUMERIC-EXPR为零(但表示为00.00、-0等)。TRUNCATE返回0(零)，不带小数位数，无论小数位数是多少。
• 如果NUMERIC-EXPR或SCALE为NULL，则TRUNCATE返回NULL。

TRUNCATE只能用作ODBC标量函数(使用花括号语法)。

ROUND可用于对数字执行类似的截断操作。Trim可用于对字符串执行类似的截断操作。

TRUNCATE, ROUND, and $JUSTIFY

TRUNCATE 和 ROUND 是执行类似操作的数值函数；它们都可用于减少数字的有效小数位数或整数位数。 ROUND 允许指定舍入（默认）或截断； TRUNCATE 不执行舍入。 ROUND 返回与 numeric-expr 相同的数据类型； TRUNCATE 返回 numeric-expr 作为数据类型 NUMERIC，除非 numeric-expr 是数据类型 DOUBLE，在这种情况下它返回数据类型 DOUBLE。

TRUNCATE 截断到指定数量的小数位数。如果截断导致尾随零，则保留这些尾随零。但是，如果 scale 大于 numeric-expr 规范形式的小数位数，则 TRUNCATE 不会填充零。

ROUND 舍入（或截断）到指定数量的小数位数，但其返回值始终是标准化的，删除尾随零。例如，ROUND(10.004,2) 返回 10，而不是 10.00。

当舍入到固定的小数位数很重要时使用 $JUSTIFY - 例如，在表示货币金额时。 $JUSTIFY 在舍入操作之后返回指定数量的尾随零。当要舍入的位数大于小数位数时，$JUSTIFY 补零。 $JUSTIFY 还右对齐数字，以便 DecimalSeparator 字符在一列数字中对齐。 $JUSTIFY 不会截断。

示例

以下两个示例都将数字截断为两位小数。第一个（使用动态 SQL）将比例指定为整数；第二个（使用嵌入式 SQL）将 scale 指定为解析为整数的主变量：

/// d ##class(PHA.TEST.SQLFunction).Truncate()
ClassMethod Truncate()
{
	s myquery = "SELECT {fn TRUNCATE(654.321888,2)} AS trunc"
	s tStatement = ##class(%SQL.Statement).%New()
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
	s rset = tStatement.%Execute()
	d rset.%Display()
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Truncate()
trunc
654.32
 
1 Rows(s) Affected

/// d ##class(PHA.TEST.SQLFunction).Truncate1()
ClassMethod Truncate1()
{
	s x=2
	&sql(
		SELECT 
			{fn TRUNCATE(654.321888,:x)}
		INTO 
			:a
	)
	if SQLCODE '= 0 { 
		w !,"Error code ",SQLCODE 
	} else { 
		w !,"truncated value is: ",a 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Truncate1()
 
truncated value is: 654.32

这两个示例都返回 654.32（截断到小数点后两位）。

以下动态 SQL 示例指定的比例大于十进制位数：

/// d ##class(PHA.TEST.SQLFunction).Truncate2()
ClassMethod Truncate2()
{
	s myquery = "SELECT {fn TRUNCATE(654.321000,9)} AS trunc"
	s tStatement = ##class(%SQL.Statement).%New()
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
	s rset = tStatement.%Execute()
	d rset.%Display()
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Truncate2()
trunc
654.321
 
1 Rows(s) Affected

它返回 654.321（ 在截断操作之前删除了尾随零；没有发生截断或零填充）。

以下动态 SQL 示例指定零比例：

/// d ##class(PHA.TEST.SQLFunction).Truncate3()
ClassMethod Truncate3()
{
	s myquery = "SELECT {fn TRUNCATE(654.321888,0)} AS trunc"
	s tStatement = ##class(%SQL.Statement).%New()
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
	s rset = tStatement.%Execute()
	d rset.%Display()
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Truncate3()
trunc
654
 
1 Rows(s) Affected

它返回 654（所有小数位和小数点都被截断）。

以下动态 SQL 示例指定负比例：

/// d ##class(PHA.TEST.SQLFunction).Truncate4()
ClassMethod Truncate4()
{
	s myquery = "SELECT {fn TRUNCATE(654.321888,-2)} AS trunc"
	s tStatement = ##class(%SQL.Statement).%New()
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
	s rset = tStatement.%Execute()
	d rset.%Display()
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Truncate4()
trunc
600
 
1 Rows(s) Affected

它返回 600（两个整数位已被截断并替换为零；请注意，未进行四舍五入）。

以下动态 SQL 示例指定与数字的整数部分一样大的负数：

/// d ##class(PHA.TEST.SQLFunction).Truncate5()
ClassMethod Truncate5()
{
	s myquery = "SELECT {fn TRUNCATE(654.321888,-3)} AS trunc"
	s tStatement = ##class(%SQL.Statement).%New()
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
	s rset = tStatement.%Execute()
	d rset.%Display()
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Truncate5()
trunc
0
 
1 Rows(s) Affected

第149章 SQL函数 TRIM

字符串函数，它返回删除了指定的前导和/或尾随字符的字符串。

大纲

TRIM([end_keyword] [characters FROM] string-expression)

参数

• end_keyword - 可选 — 一个关键字，指定要剥离的字符串表达式的哪一端。可用值为 LEADING、TRAILING 或 BOTH。默认为两者。
• characters - 可选 — 一个字符串表达式，指定要从字符串表达式中删除的字符。指定字符的每个实例都从指定的末端被剥离，直到遇到此处未指定的字符。因此TRIM(BOTH 'ab' FROM 'bbbaacaaa') 返回 ‘c’。在此示例中，BOTH 关键字是可选的。如果未指定字符，TRIM 会去除空格。如果指定了字符，则需要 FROM 关键字。如果指定了 end_keyword 而未指定字符，则允许使用 FROM 关键字（但不是必需的）。如果没有指定这些参数，则不允许使用 FROM 关键字。
• string-expression - 要剥离的字符串表达式。字符串表达式可以是列名、字符串文字或另一个函数的结果，其中基础数据类型可以表示为任何字符类型（例如 CHAR 或 VARCHAR2）。如果省略了字符和 end_keyword，则省略 FROM 关键字。

描述

TRIM 从提供的值的开头和/或结尾去除指定的字符。默认情况下，去除字母是区分大小写的。当遇到未在字符中指定的字符时，从任一端停止字符剥离。默认是从字符串表达式的两端去除空格。

无论要修剪的输入表达式的数据类型如何，TRIM 始终返回数据类型 VARCHAR。

请注意，在将数字提供给 TRIM 或任何其他 SQL 函数之前，它们会自动从数字中去除前导零。要保留前导零，必须将数字指定为字符串。

可选的 end_keyword 参数可以采用以下值：

• LEADING - 关键字，指定要从 string-expression 的开头删除 characters 中的字符。
• TRAILING - 关键字，指定要从 string-expression 的末尾删除 characters 中的字符。
• BOTH - 关键字，指定要从 string-expression 的开头和结尾删除 characters 中的字符。 BOTH 是默认值，如果未指定 end_keyword 则使用。

或者，可以使用 LTRIM 修剪前导空白，或使用 RTRIM 修剪尾随空白。

要使用前导或尾随空格或其他字符填充字符串，请使用 LPAD 或 RPAD。

可以使用 LENGTH 函数来确定是否已从字符串中删除或添加空格。

要剥离的字符

• 所有字符：如果字符包含 string-expression 中的所有字符，则 TRIM 返回一个空字符串。

• 单引号字符：如果单引号字符在字符和字符串表达式中都是双引号，则Trim可以删减这些字符。因此，TRIM(BOTH 'a''b' FROM 'bb''ba''acaaa''')返回’c‘。

• 空格：如果省略字符，TRIM 会从字符串表达式中修剪空格。如果指定了字符，则必须包含空格字符以去除空格。

• %List：如果 string-expression 是 %List，TRIM 只能修剪尾随字符，不能修剪前导字符。这是因为 %List 包含前导编码字符。必须将 %List 转换为字符串才能将 TRIM 应用于前导字符。

• NULL：如果任一字符串表达式为 NULL，TRIM 将返回 NULL。

示例

以下示例使用 end_keyword 和 characters 默认值；它从“abc”中删除前导和尾随空格。选择项将“^”连接到字符串的两端以显示空白。

SELECT '^'||'   abc   '||'^' AS UnTrimmed,'^'||TRIM('   abc   ')||'^' AS Trimmed


^   abc   ^	^abc^

以下示例都是从字符串表达式中去除前导空格的有效语法：

SELECT TRIM(LEADING '   abc   '),TRIM(LEADING FROM '   def   '),TRIM(LEADING ' ' FROM '   ghi   ')

abc   	def   	ghi   

以下示例从字符串“xxxabcxxx”的开头删除字符“x”，得到“abcxxx”：

SELECT TRIM(LEADING 'x' FROM 'xxxabcxxx') AS Trimmed

abcxxx

以下示例均删除了“xxxabcxxx”开头和结尾的字符“x”，结果为“abc”。第一个指定 BOTH，第二个将 BOTH 作为默认值：

SELECT TRIM(BOTH 'x' FROM 'xxxabcxxx') AS Trimmed

abc

SELECT TRIM('x' FROM 'xxxabcxxx') AS Trimmed

abc

以下示例从“abcxzzxyyyyz”的末尾删除字符“xyz”的所有实例，从而产生“abc”：

SELECT TRIM(TRAILING 'xyz' FROM 'abcxzzxyyyyz') AS Trimmed.

abc

下面的示例去掉FirstName中的所有字母，返回前面有空格的姓氏，从而修剪FullName。例如FirstName/Fullname‘fred’/‘fred Rogers’返回‘Rogers’。在本例中，FirstName‘Annie’将从LastName中去掉‘Ann’、‘Anne’、‘Ani’、‘Ain’、‘Annee’或‘Annie’，但不会完全去掉‘Anna’，因为Trim区分大小写；只会去掉‘A’，而不是‘a’。

SELECT TRIM(LEADING FirstName FROM FullName) FROM Sample.Person

以下示例从FavoriteColors值中删除前导字母"B"或"R"。请注意，必须将列表转换为字符串，才能对前导字符应用剪裁：

SELECT TOP 15 Name,FavoriteColors,
       TRIM(LEADING 'BR' FROM $LISTTOSTRING(FavoriteColors)) AS Trimmed
       FROM Sample.Person WHERE FavoriteColors IS NOT NULL

第147章 SQL函数 TO_TIMESTAMP

将格式化字符串转换为时间戳的日期函数。

大纲

TO_TIMESTAMP(date_string[,format])

参数

• date_string - 要转换为时间戳的字符串表达式。此表达式可能包含日期值、时间值或日期和时间值。
• format - 可选 — 对应于 date_string 的日期和时间格式字符串。如果省略，则默认为 DD MON YYYY HH:MI:SS。

描述

TO_TIMESTAMP 函数将各种格式的日期和时间字符串转换为标准时间戳，数据类型为 TIMESTAMP。 TO_TIMESTAMP 返回具有以下格式的时间戳：

yyyy-mm-dd hh:mm:ss

始终包括前导零。时间使用 24 小时制指定。默认情况下，返回的时间戳不包括小数秒。

注意：TO_TIMESTAMP 以 ODBC 格式返回标准时间戳。 TO_POSIXTIME 返回一个编码的 64 位时间戳。 TO_POSIXTIME 是新编程的推荐时间戳格式。

必须指定匹配的 date_string 和格式。如果省略格式，则 date_string 必须匹配 DD MON YYYY HH:MI:SS。

如果 date_string 省略了时间戳的组成部分，则 TO_TIMESTAMP 提供缺少的组成部分。如果 date_string 和 format 都省略了年份，则 yyyy 默认为当前年份；如果只有 date_string 省略了年份，则默认为 00，根据年份格式元素扩展为四位数年份。如果省略日或月值，则 dd 默认为 01； mm-dd 默认为 01-01。因此，如果 date_string 和 format 都省略了时间戳的日期部分，则 TO_TIMESTAMP 默认为当年的 1 月 1 日，采用 ODBC 格式：yyyy-01-01。

缺少的时间组件默认为 00。支持小数秒，但必须明确指定；默认情况下不提供小数秒。

TO_TIMESTAMP 支持将两位数年份转换为四位数。 TO_TIMESTAMP 支持将 12 小时制时间转换为 24 小时制时间。它提供日期和时间元素值的范围验证，包括闰年验证。范围验证违规会生成 SQLCODE -400 错误。

也可以使用 TOTIMESTAMP() 方法调用从 ObjectScript 调用此函数：

$SYSTEM.SQL.Functions.TOTIMESTAMP(date_string,format)

在为时间戳字段提供默认值时，可以在数据定义中使用 TO_TIMESTAMP 函数。例如：

CREATE TABLE Sample.MyEmpReviews
(EmpNum INTEGER UNIQUE NOT NULL,
 ReviewDate TIMESTAMP DEFAULT TO_TIMESTAMP(365,'DDD'))

在此示例中，插入记录的用户可以提供 ReviewDate 值，不提供 ReviewDate 值并获取当年第 365 天的默认时间戳，或者提供 NULL 的 ReviewDate 并获取 NULL。

TO_TIMESTAMP 可以与 CREATE TABLE 或 ALTER TABLE ADD COLUMN 语句一起使用。在此上下文中只能使用 date_string 的文字值。

相关 SQL 函数

• TO_TIMESTAMP 将格式化的日期和时间字符串转换为标准时间戳。
• TO_CHAR 执行相反的操作；它将标准时间戳转换为格式化的日期和时间字符串。
• TO_DATE 将格式化的日期字符串转换为日期整数。
• CAST 和 CONVERT 执行 TIMESTAMP 数据类型转换。

日期和时间字符串

date_string 参数指定日期和时间字符串文字。如果提供没有时间分量的日期字符串，则 TO_TIMESTAMP 提供时间值 00:00:00。如果您提供不带日期组件的时间字符串，则 TO_TIMESTAMP 提供当年 01–01（1 月 1 日）的日期。

可以为输入 date_string 提供任何类型的日期和时间字符串。每个 date_string 字符必须对应于格式字符串，但以下情况除外：

• 可以包含或省略前导零（不带分隔符的 date_string 除外）。
• 年份可以用两位数或四位数字指定。
• 月份缩写（采用 MON 格式）必须与该区域设置的月份缩写相匹配。对于某些语言环境，月份缩写可能不是月份名称的初始连续字符。月份缩写不区分大小写。
• 月份名称（格式为 MONTH）应指定为完整的月份名称。但是，TO_TIMESTAMP 不需要格式为 MONTH 的完整月份名称；它接受完整月份名称的初始字符，并选择月份列表中与该初始字母序列相对应的第一个月。因此，在英语中，“J” = “January”，“Ju” = “June”，“Jul” = “July”。指定的所有字符必须与完整月份名称的连续字符匹配；不检查完整月份名称之外的字符。例如，“Fe”、“Febru”和“FebruaryLeap”都是有效值； “Febs”不是有效值。月份名称不区分大小写。
• 可以使用为语言环境定义的时间分隔符输入时间值。输出时间戳始终表示带有 ODBC 标准时间分隔符的时间值：冒号 (:) 表示小时、分钟和秒，句点 (.) 表示小数秒。省略的时间元素默认为零。默认情况下，返回的时间戳不带小数秒。

格式化

格式是根据以下规则指定的一个或多个格式元素的字符串：

• 格式元素不区分大小写。
• 几乎任何顺序或数量的格式元素都是允许的。
• 格式字符串使用与 date_string 中的分隔符匹配的非字母数字分隔符（例如，空格、斜杠或连字符）分隔它们的元素。这些分隔符不会出现在使用标准时间戳分隔符的输出字符串中：连字符表示日期值，冒号表示时间值，句点（如果需要）表示小数秒。这种分隔符的使用不依赖于为您的 NLS 语言环境定义的 DateSeparator。
• 以下日期格式字符串不需要分隔符：MMDDYYYY、DDMMYYYY、YYYYMMDDHHMISS、YYYYMMDDHHMI、YYYYMMDDHH、YYYYMMDD、YYYYDDMM、HHMISS 和 HHMI。还支持不完整的日期格式 YYYYMM，并假定 DD 值为 01。请注意，在这些情况下，必须为所有元素（例如 MM 和 DD）提供前导零，但最后一个元素除外。
• 格式中不是有效格式元素的字符将被忽略。

格式元素

下表列出了 format 参数的有效日期格式元素：

TO_TIMESTAMP 格式还可以包括 D（星期几号）、DY（星期几缩写）或 DAY（星期几名称）元素以匹配输入的 date_string。但是，这些格式元素未经过验证或用于确定返回值。

两位数年份转换（RR 和 RRRR 格式）

RR 格式提供两位数到四位数的年份转换。此转换基于当年。如果当前年份在上半世纪（例如，2000 年到 2050 年），则从 00 到 49 的两位数年份扩展到当前世纪的四位数年份，从 50 到 2 位数的年份99 年扩大到上个世纪的四位数年份。如果当前年份在世纪下半叶（例如，2050 年到 2099 年），则所有两位数年份都将扩展为当前世纪中的四位数年份。将两位数年份扩展到四位数年份如下例所示：

SELECT TO_TIMESTAMP('29 September 00','DD MONTH RR'),
       TO_TIMESTAMP('29 September 18','DD MONTH RR'),
       TO_TIMESTAMP('29 September 49','DD MONTH RR'),
       TO_TIMESTAMP('29 September 50','DD MONTH RR'),
       TO_TIMESTAMP('29 September 77','DD MONTH RR')
       
2000/9/29 0:00:00	2018/9/29 0:00:00	2049/9/29 0:00:00	1950/9/29 0:00:00	1977/9/29 0:00:00

RRRR 格式允许您输入两位数和四位数字的混合年份。四位数年份不变（与 YYYY 相同）。使用 RR 格式算法将两位数年份转换为四位数年份。这在以下示例中显示：

SELECT TO_TIMESTAMP('29 September 2018','DD MONTH RRRR')AS FourDigit,
       TO_TIMESTAMP('29 September 18','DD MONTH RRRR') AS TwoDigit,
       TO_TIMESTAMP('29 September 1949','DD MONTH RRRR') AS FourDigit,
       TO_TIMESTAMP('29 September 49','DD MONTH RRRR') AS TwoDigit,
       TO_TIMESTAMP('29 September 1950','DD MONTH RRRR') AS FourDigit,
       TO_TIMESTAMP('29 September 50','DD MONTH RRRR') AS TwoDigit
       
2018/9/29 0:00:00	2018/9/29 0:00:00	1949/9/29 0:00:00	2049/9/29 0:00:00	1950/9/29 0:00:00	1950/9/29 0:00:00

一年中的某一天（DDD 格式）

可以使用 DDD 将一年中的某一天（自 1 月 1 日以来经过的天数）转换为实际日期。格式字符串 DDD YYYY 必须与由整数天数和四位数年份组成的相应 date_string 配对。 （与 DDD 一起使用时，两位数的年份必须指定为 RR（而不是 YY）。）格式字符串 DDD 默认为当前年份。经过的天数必须是 1 到 365 范围内的正整数（如果 YYYY 是闰年，则为 366）。四位数年份必须在年份日期范围内：0001 到 9999。（如果省略年份，则默认为当前年份。） DDD 和年份（YYYY、RRRR 或 RR）格式元素可以在任何命令；它们之间的分隔符是强制性的；此分隔符可以是空格。以下示例显示了这一年中的一天的用法：

SELECT TO_TIMESTAMP('2018:160','YYYY:DDD')

2018/6/9 0:00:00

如果格式字符串同时包含 DD 和 DDD 元素，则 DDD 元素占主导地位。这在以下示例中显示，它返回 2008-02-29 00:00:00（不是 2008-12-31 00:00:00）：

SELECT TO_TIMESTAMP('2018-12-31-60','YYYY-MM-DD-DDD')

2018/3/1 0:00:00

TO_TIMESTAMP 允许返回对应于一年中某一天的日期表达式。 TO_CHAR 允许返回与日期表达式对应的一年中的哪一天。

第一年之前的日期

TO_TIMESTAMP 和 TO_POSIXTIME 可以表示追溯到 0001 年 1 月 1 日的日期。

TO_DATE 提供儒略日期格式，它可以表示追溯到公元前 4712 年 1 月 1 日的日期。儒略日期转换将七位内部正整数值（儒略日计数）转换为显示格式或 ODBC 格式的日期。儒略日期不支持时间值。

12 小时制时间

• date_string 时间值后跟“am”或“pm”（没有句点）。这些子午线指标不区分大小写，可以附加到时间值后，也可以用一个或多个空格分隔。
• 该格式遵循具有'a.m.'或'p.m.'元素（任一个）的时间格式，与时间格式分开一个或多个空格。例如：DD-MON-YYYY-HH:MI:SS。FF P.M.此格式支持12小时时钟日期串值，例如下午2:23,2:23:54.6pm，下午2:23:54，下午2:23:54和下午2:23:54（假设为上午）。子午线指标不区分大小写。当使用带有周期的子午线指示器时，必须将其与时间值分开一个或多个空格。

示例

以下嵌入式 SQL 示例以各种格式指定日期字符串。第一个使用默认格式，其他指定格式。所有这些都将 date_string 转换为 2018–06–29 00:00:00 的时间戳值：

/// d ##class(PHA.TEST.SQLFunction).ToTimestamp()
ClassMethod ToTimestamp()
{
	&sql(
		SELECT
			TO_TIMESTAMP('29 JUN 2018'),
			TO_TIMESTAMP('2018 Jun 29','YYYY MON DD'),
			TO_TIMESTAMP('JUNE 29, 2018','month dd, YYYY'),
			TO_TIMESTAMP('2018***06***29','YYYY***MM***DD'),
			TO_TIMESTAMP('06/29/2018','MM/DD/YYYY'),
			TO_TIMESTAMP('29/6/2018','DD/MM/YYYY')
		INTO :a,:b,:c,:d,:e,:f
	)
	if SQLCODE = 0 { 
		w !,a,!,b,!,c,!,d,!,e,!,f 
	} else { 
		w "SQLCODE error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToTimestamp()
 
2018-06-29 00:00:00
2018-06-29 00:00:00
2018-06-29 00:00:00
2018-06-29 00:00:00
2018-06-29 00:00:00
2018-06-29 00:00:00

以下示例指定 YYYYMM 日期格式。它不需要元素分隔符。 TO_TIMESTAMP 提供缺失的日期和时间值：

 SELECT TO_TIMESTAMP('201806','YYYYMM')
 
 2018/6/1 0:00:00

以下示例仅指定 HH:MI:SS.FF 时间格式。 TO_TIMESTAMP 提供缺失的日期值。在每种情况下，此示例都返回 2018-01-01 的日期（其中 2018 是当前年份）：

SELECT TO_TIMESTAMP('11:34','HH:MI:SS.FF'),
       TO_TIMESTAMP('11:34:22','HH:MI:SS.FF'),
       TO_TIMESTAMP('11:34:22.00','HH:MI:SS.FF'),
       TO_TIMESTAMP('11:34:22.7','HH:MI:SS.FF'),
       TO_TIMESTAMP('11:34:22.7000000','HH:MI:SS.FF')

2022/1/1 11:34:00	2022/1/1 11:34:22	2022/1/1 11:34:22	2022/1/1 11:34:22	2022/1/1 11:34:22

请注意，小数秒完全按照指定传递，没有填充或截断。

以下示例显示了一些其他方法来指定带小数秒的时间格式：

SELECT TO_TIMESTAMP('113422.9678','HHMISS.FF'),
       TO_TIMESTAMP('9678.113422','FF.HHMISS'),
       TO_TIMESTAMP('9678.20170804113422','FF.YYYYMMDDHHMISS')
       
2022/1/1 11:34:22	2022/1/1 11:34:22	2017/8/4 11:34:22

TO_TIMESTAMP 的所有三个调用都返回一个 ODBC 格式的时间戳，其时间部分的值为 11:34:22.9678。对于前两个，省略的日期部分默认为当年的 1 月 1 日；第三个提供日期部分值。

第146章 SQL函数 TO_POSIXTIME

注：此函数在IRIS版本可用，Cache不可用。

将格式化的日期字符串转换为 %PosixTime 时间戳的日期/时间函数。

大纲

TO_POSIXTIME(date_string[,format])

参数

• date_string - 要转换为 %PosixTime 时间戳的字符串表达式。此表达式可能包含日期值、时间值或日期和时间值。
• format - 可选 — 对应于 date_string 的日期和时间格式字符串。如果省略，则默认为 DD MON YYYY HH:MI:SS。

描述

TO_POSIXTIME 函数将各种格式的日期和时间字符串转换为 %PosixTime 时间戳，数据类型为 %Library.PosixTime。 TO_POSIXTIME 返回 %PosixTime 时间戳作为计算值，该值基于从 1970-01-01 00:00:00 的任意起点经过的秒数，编码为 64 位有符号整数。从该日期开始经过的实际秒数（和小数秒）是 Unix®timestamp，一个数值。 对 Unix® 时间戳进行编码以生成 %PosixTime 时间戳。由于 %PosixTime 时间戳值已编码，因此 1970-01-01 00:00:00 表示为 1152921504606846976。1970-01-01 00:00:00 之前的日期具有负整数值。

TO_POSIXTIME 不转换时区；本地日期和时间转换为本地 %PosixTime 时间戳； UTC 日期和时间将转换为 UTC%PosixTime 时间戳。

%PosixTime 支持的最早日期为 0001-01-01 00:00:00，其逻辑值为 -6979664624441081856。支持的最后日期为 9999-12-3123:59:59.999999，其逻辑值为 1406323805406846975。这些限制对应于 ODBC 日期格式显示限制。可以使用 %Library.PosixTime MINVAL 和 MAXVAL 参数进一步限制这些值。可以使用 IsValid() 方法来确定数值是否为有效的 %PosixTime 值。

%PosixTime 值始终编码小数秒的 6 位十进制数字的精度。精度位数较少的 date_string 在 %PosixTime 转换之前被零填充到 6 位；在 %PosixTime 转换之前，精度超过 6 位的 date_string 会被截断为 6 位。

如果 date_string 省略了时间戳的组成部分，则 TO_POSIXTIME 提供缺少的组成部分。如果 date_string 和 format 都省略了年份，则 yyyy 默认为当前年份；如果只有 date_string 省略了年份，则默认为 00，根据年份格式元素扩展为四位数年份。如果省略日或月值，则 dd 默认为 01； mm-dd 默认为 01-01。缺少的时间组件默认为 00。支持小数秒，但必须明确指定；默认情况下不提供小数秒。

TO_POSIXTIME 支持将两位数年份转换为四位数。 TO_POSIXTIME 支持将 12 小时制时间转换为 24 小时制时间。它提供日期和时间元素值的范围验证，包括闰年验证。范围验证违规会生成 SQLCODE -400 错误。

也可以使用 TOPOSIXTIME() 方法调用从 ObjectScript 调用此函数：

$SYSTEM.SQL.Functions.TOPOSIXTIME(date_string,format)

TO_POSIXTIME 函数可在为字段提供默认值时用于数据定义。例如：

CREATE TABLE mytest
(ID NUMBER(12,0) NOT NULL,
End_Year DATE DEFAULT TO_POSIXTIME('12-31-2018','MM-DD-YYYY') NOT NULL)

TO_POSIXTIME 可以与 CREATE TABLE 或 ALTER TABLE ADD COLUMN 语句一起使用。在此上下文中只能使用 date_string 的文字值。

%PosixTime 表示

%PosixTime 对小数秒的 6位精度进行编码，无论 date_string 的精度如何。 ODBC 和显示模式截断尾随零的精度。

• 逻辑模式：编码的 64 位（19 个字符）有符号整数。
• ODBC 模式：YYYY–MM–DD HH:MM:SS.FFFFFF。
• 显示模式：使用当前语言环境的默认日期/时间格式（dformat -1 和 tformat -1），如 $ZDATETIME 中所述。

相关 SQL 函数

• TO_POSIXTIME 将格式化的日期和时间字符串转换为 %PosixTime 时间戳。
• TO_CHAR 执行相反的操作；它将 %PosixTime 时间戳转换为格式化的日期和时间字符串。
• UNIX_TIMESTAMP 将格式化的日期和时间字符串转换为 Unix® 时间戳。
• TO_DATE 将格式化的日期字符串转换为日期整数。
• CAST 和 CONVERT 执行 %PosixTime 数据类型转换。

日期和时间字符串

date_string 参数指定日期和时间字符串文字。如果提供没有时间分量的日期字符串，则 TO_POSIXTIME 提供时间值 00:00:00。如果提供不带日期组件的时间字符串，则 TO_POSIXTIME 提供当年的 01–01（1 月 1 日）日期。

可以为输入 date_string 提供任何类型的日期和时间字符串。每个 date_string 字符必须对应于格式字符串，但以下情况除外：

• 可以包含或省略前导零（不带分隔符的 date_string 除外）。

• 年份可以用两位数或四位数字指定。

• 月份缩写（采用 MON 格式）必须与该区域设置的月份缩写相匹配。对于某些语言环境，月份缩写可能不是月份名称的初始连续字符。月份缩写不区分大小写。

• 月份名称（格式为 MONTH）应指定为完整的月份名称。但是，TO_POSIXTIME 不需要格式为 MONTH 的完整月份名称；它接受完整月份名称的初始字符，并选择月份列表中与该初始字母序列相对应的第一个月。因此，在英语中，“J” = “January”，“Ju” = “June”，“Jul” = “July”。指定的所有字符必须与完整月份名称的连续字符匹配；不检查完整月份名称之外的字符。例如，“Fe”、“Febru”和“FebruaryLeap”都是有效值； “Febs”不是有效值。月份名称不区分大小写。

• 可以使用为语言环境定义的时间分隔符输入时间值。输出时间戳始终表示带有 ODBC 标准时间分隔符的时间值：冒号 (:) 和句点 (.))。省略的时间元素默认为零。

格式化

格式是根据以下规则指定的一个或多个格式元素的字符串：

• 格式元素不区分大小写。
• 几乎任何顺序或数量的格式元素都是允许的。
• 格式字符串使用与 date_string 中的分隔符匹配的非字母数字分隔符（例如，空格、斜杠或连字符）分隔它们的元素。这些分隔符不会出现在使用标准时间戳分隔符的输出字符串中：连字符表示日期值，冒号表示时间值，句点（如果需要）表示小数秒。这种分隔符的使用不依赖于为 NLS 语言环境定义的 DateSeparator。
• 以下日期格式字符串不需要分隔符：MMDDYYYY、DDMMYYYY、YYYYMMDDHHMISS、YYYYMMDDHHMI、YYYYMMDDHH、YYYYMMDD、YYYYDDMM、HHMISS 和 HHMI。还支持不完整的日期格式 YYYYMM，并假定 DD 值为 01。请注意，在这些情况下，必须为所有元素（例如 MM 和 DD）提供前导零，但最后一个元素除外。
• 格式中不是有效格式元素的字符将被忽略。

格式元素

下表列出了 format 参数的有效日期格式元素：

TO_POSIXTIME 格式还可以包含 D（星期几号）、DY（星期几缩写）或 DAY（星期几名称）元素以匹配输入 date_string。但是，这些格式元素未经过验证或用于确定返回值。

两位数年份转换（RR 和 RRRR 格式）

RR 格式提供两位数到四位数的年份转换。 TO_POSIXTIME 使用默认日期格式 (dformat -1) 执行此转换，该格式使用当前语言环境的 YearOption 属性，如 $ZDATETIME 中所述。

一年中的某一天（DDD 格式）

可以使用 DDD 将一年中的某一天（自 1 月 1 日以来经过的天数）转换为实际日期。格式字符串 DDD YYYY 必须与由整数天数和四位数年份组成的相应 date_string 配对。 （与 DDD 一起使用时，两位数的年份必须指定为 RR（而不是 YY）。）格式字符串 DDD 默认为当前年份。经过的天数必须是 1 到 365 范围内的正整数（如果 YYYY 是闰年，则为 366）。四位数年份必须在标准 日期范围内：1841 到 9999。（如果省略年份，则默认为当前年份。）DDD 和年份（YYYY、RRRR 或 RR）格式元素可以是以任何顺序指明；它们之间的分隔符是强制性的；此分隔符可以是空格。以下示例显示了这一年中的一天的用法：

SELECT TO_POSIXTIME('2018:160','YYYY:DDD')


2018-06-09 00:00:00

如果格式字符串同时包含 DD 和 DDD 元素，则 DDD 元素占主导地位。这在以下示例中显示，它返回 2008-02-2900:00:00（不是 2008-12-31 00:00:00）：

SELECT TO_POSIXTIME('2018-12-31-60','YYYY-MM-DD-DDD')

2018-03-01 00:00:00

TO_POSIXTIME 允许返回对应于一年中某一天的日期表达式。 TO_CHAR 允许返回与日期表达式对应的一年中的哪一天。

1970 年之前的日期

TO_POSIXTIME 将 1970 年 1 月 1 日之前的日期表示为负数。 %PosixTime 不能表示 0001 年 1 月 1 日之前或 9999 年 12 月 31 日之后的日期。尝试输入这样的日期会导致 SQLCODE -400 错误。 TO_DATE 函数提供儒略日期格式来表示 0001 年 1 月 1 日之前的 BCE 日期。儒略日期转换将七位内部正整数值（儒略日计数）转换为显示格式或 ODBC 格式日期。儒略日期不支持时间值。

12 小时制时间

%PosixTime 时间戳始终表示使用 24 小时制的时间。 date_string 可以使用 12 小时制或 24 小时制表示时间。 TO_POSIXTIME 假定为 24 小时制，除非以下情况之一适用：

• date_string 时间值后跟“am”或“pm”（没有句点）。这些子午线指标不区分大小写，可以附加到时间值后，也可以用一个或多个空格分隔。
• 格式遵循带有“a.m.”``'p.m.'元素（任意一个），与时间格式之间用一个或多个空格分隔。例如：DD MON YYYY HH:MI:SS.FF P.M.此格式支持 12 小时制 date_string 值，例如 2:23pm、2:23:54.6pm、2:23:54 pm、2:23:54 pm 和 2:23:54（假定为 AM）。经络指标不区分大小写。当使用带有句点的经络指标时，它必须与时间值隔开一个或多个空格。

示例

以下嵌入式 SQL 示例将当前本地日期时间转换为 %PosixTime 值。 （请注意，格式使用“ff”表示任意数量的小数位；在这种情况下，精度为 3 位。%PosixTime 将其编码为 6 位精度，提供三个尾随零。）然后此示例使用 %Posix LogicalToOdbc( ) 方法将此值显示为 ODBC 时间戳，修剪尾随零精度：

/// d ##class(PHA.TEST.SQLFunction).ToPosixtime()
ClassMethod ToPosixtime()
{
	s tstime = $ZDATETIME($ZTIMESTAMP,3,1,3)
	w "local datetime in : ",tstime,!
	&sql(
		SELECT
			TO_POSIXTIME(:tstime,'yyyy-mm-dd hh:mi:ss.ff')
		INTO 
			:ptime
	)
	if SQLCODE=0 { 
		w "Posix encoded datetime: ",ptime,!
		s ODBCout=##class(%PosixTime).LogicalToOdbc(ptime)
		w "local datetime out: ",ODBCout 
	} else { 
		w "SQLCODE error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToPosixtime() 
local datetime in : 2022-05-10 02:00:10.057
Posix encoded datetime: 1154573652616903976
local datetime out: 2022-05-10 02:00:10.057

以下嵌入式 SQL 示例以各种格式指定日期字符串。第一个使用默认格式，其他指定格式。所有这些都将 date_string 转换为 2018–06–29 00:00:00 的时间戳值：

/// d ##class(PHA.TEST.SQLFunction).ToPosixtime1()
ClassMethod ToPosixtime1()
{
	&sql(
		SELECT
			TO_POSIXTIME('29 JUN 2018'),
			TO_POSIXTIME('2018 Jun 29','YYYY MON DD'),
			TO_POSIXTIME('JUNE 29, 2018','month dd, YYYY'),
			TO_POSIXTIME('2018***06***29','YYYY***MM***DD'),
			TO_POSIXTIME('06/29/2018','MM/DD/YYYY'),
			TO_POSIXTIME('29/6/2018','DD/MM/YYYY')
		INTO 
			:a,:b,:c,:d,:e,:f
	)
	if SQLCODE = 0 { 
		w !,a,!,b,!,c,!,d,!,e,!,f 
	} else { 
		w "SQLCODE error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToPosixtime1()

1154451735006846976
1154451735006846976
1154451735006846976
1154451735006846976
1154451735006846976
1154451735006846976

以下示例指定 YYYYMM 日期格式。它不需要元素分隔符。 TO_POSIXTIME 提供缺失的日期和时间值：

 SELECT TO_POSIXTIME('201806','YYYYMM')
 
 2018-06-01 00:00:00

以下示例仅指定 HH:MI:SS.FF 时间格式。 TO_POSIXTIME 提供缺失的日期值。在每种情况下，此示例都返回 2018-01-01 的日期（其中 2018 是当前年份）：

SELECT TO_POSIXTIME('11:34','HH:MI:SS.FF'),
       TO_POSIXTIME('11:34:22','HH:MI:SS.FF'),
       TO_POSIXTIME('11:34:22.00','HH:MI:SS.FF'),
       TO_POSIXTIME('11:34:22.7','HH:MI:SS.FF'),
       TO_POSIXTIME('11:34:22.7000000','HH:MI:SS.FF')

2022-01-01 11:34:00	2022-01-01 11:34:22	2022-01-01 11:34:22	2022-01-01 11:34:22.7	2022-01-01 11:34:22.7

请注意，小数秒完全按照指定传递，没有填充或截断。

第145章 SQL函数 TO_NUMBER

将字符串表达式转换为 NUMERIC 数据类型的值的字符串函数。

大纲

TO_NUMBER(string-expression)

TONUMBER(string-expression)

参数

• string-expression - 要转换的字符串表达式。表达式可以是列名、字符串文字或另一个函数的结果，其中基础数据类型为 CHAR 或 VARCHAR2 类型。

描述

名称 TO_NUMBER 和 TONUMBER 可以互换。它们支持 Oracle 兼容性。

TO_NUMBER 将字符串表达式转换为数字数据类型 NUMERIC。但是，如果 string-expression 的数据类型为 DOUBLE，则 TO_NUMBER 返回一个数据类型为 DOUBLE 的数字。

TO_NUMBER 转换采用数字字符串并通过解析加号和减号、扩展指数符号（“E”或“e”）以及删除前导零将其转换为规范数字。 TO_NUMBER 在遇到非数字字符（例如字母或数字组分隔符）时停止转换。因此字符串 '7dwarves' 转换为 7。如果 string-expression 的第一个字符是非数字字符串，则 TO_NUMBER 返回 0。如果 string-expression 是空字符串 ('')，则 TO_NUMBER 返回 0。TO_NUMBER 将 -0 解析为 0 . TO_NUMBER 不解析算术运算。因此字符串 '2+4' 转换为 2。如果为字符串表达式指定 NULL，则 TO_NUMBER 返回 null。

NUMERIC 数据类型的默认 SCALE 为 2。因此，在 DISPLAY 模式下选择此值时，TO_NUMBER 始终显示返回值，保留 2 位小数。额外的小数位数四舍五入到小数点后两位；尾随零被解析为两位小数。当通过 xDBC 使用 TO_NUMBER 时，它还返回类型为 NUMERIC，SCALE 为 2。在 LOGICAL 模式或 ODBC 模式下，返回值是规范数字；没有对小数位施加比例，并且省略了尾随零。

相关 SQL 函数

• TO_NUMBER 将字符串转换为数字数据类型 NUMERIC。
• CAST 和 CONVERT 可用于将字符串转换为任意数据类型的数字。例如，可以将一个字符串转换为多个数据类型 INTEGER。
• TO_DATE 将格式化的日期字符串转换为日期整数。
• TO_TIMESTAMP 将格式化的日期和时间字符串转换为标准时间戳。

示例

以下两个示例显示 TO_NUMBER 如何将字符串转换为数字，然后将其作为具有适当 SCALE 的数据类型 NUMERIC 返回。第一个示例在显示模式下返回数字，第二个示例在逻辑模式下返回数字：

/// d ##class(PHA.TEST.SQLFunction).ToNumber()
ClassMethod ToNumber()
{
	s myquery = "SELECT TO_NUMBER('+-+-0123.0093degrees')"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=2
	s qStatus = tStatement.%Prepare(myquery)
	s rset = tStatement.%Execute()
	d rset.%Display()  // Display mode value: 123.01
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToNumber()
Expression_1
123.01
 
1 Rows(s) Affected

/// d ##class(PHA.TEST.SQLFunction).ToNumber1()
ClassMethod ToNumber1()
{
	s myquery = "SELECT TO_NUMBER('+-+-0123.0093degrees')"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=0
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
	s rset = tStatement.%Execute()
	d rset.%Display()   // Logical mode value: 123.0093
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToNumber1()
Expression_1
123.0093
 
1 Rows(s) Affected

以下示例显示当 string-expression 为 DOUBLE 数据类型时，TO_NUMBER 将值作为数据类型 DOUBLE 返回：

/// d ##class(PHA.TEST.SQLFunction).ToNumber2()
ClassMethod ToNumber2()
{
	s myquery = "SELECT TO_NUMBER(CAST('+-+-0123.0093degrees' AS DOUBLE))"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=2
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
	s rset = tStatement.%Execute()
	d rset.%Display()  // Display mode value
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToNumber2()
Expression_1
123.00929999999999608
 
1 Rows(s) Affected

/// d ##class(PHA.TEST.SQLFunction).ToNumber3()
ClassMethod ToNumber3()
{
	s myquery = "SELECT TO_NUMBER(CAST('+-+-0123.0093degrees' AS DOUBLE))"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=0
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
	s rset = tStatement.%Execute()
	d rset.%Display()   // Logical mode value
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToNumber3()
Expression_1
123.00929999999999608
 
1 Rows(s) Affected

以下示例显示如何使用 TO_NUMBER 列出按数字升序排列的街道地址：

SELECT Home_Street,Name
FROM Sample.Person
ORDER BY TO_NUMBER(Home_Street)

将结果与按字符串升序排列的相同数据进行比较：

SELECT Home_Street,Name
FROM Sample.Person
ORDER BY Home_Street

我们很高兴地宣布，从7.2.4版本开始，DBeaver已经支持开箱即用InterSystems IRIS的。 你不需要再手动配置它，只要在连接列表中找到IRIS的图标即可。

第144章 SQL函数 TO_DATE（二）

一年中的某一天（DDD 格式）

可以使用 DDD 将一年中的某一天（自 1 月 1 日以来经过的天数）转换为实际日期。格式字符串 DDDYYYY 必须与由整数天数和四位数年份组成的相应 date_string 配对。 （与 DDD 一起使用时，两位数的年份必须指定为 RR（而不是 YY）。）格式字符串 DDD 默认为当前年份。经过的天数必须是 1 到 365 范围内的正整数（如果 YYYY 是闰年，则为 366）。四位数年份必须在标准 日期范围内：1841 到 9999。DDD 和 YYYY 格式元素可以按任意顺序指定；它们之间的分隔符是强制性的。以下示例显示了这一年中的一天的用法：

/// d ##class(PHA.TEST.SQLFunction).ToDate4()
ClassMethod ToDate4()
{
	NEW SQLCODE
	&sql(
		SELECT TO_DATE('2018:60','YYYY:DDD')
		INTO :a
	)
	if SQLCODE = 0 {
		w a," = ",$ZDATE(a,1,,4) 	
	} else { 
		w "error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToDate4()
64708 = 03/01/2018

如果格式字符串同时包含 DD 和 DDD 元素，则 DDD 元素占主导地位。这在以下示例中显示，该示例返回 2/29/2020（不是 12/31/2020）：

/// d ##class(PHA.TEST.SQLFunction).ToDate4()
ClassMethod ToDate5()
{
	n SQLCODE
	&sql(
		SELECT TO_DATE('2020-12-31-60','YYYY-MM-DD-DDD')
		INTO :a
	)
	if SQLCODE = 0 {
		w a," = ",$ZDATE(a,1,,4) 	
	} else { 
		w "error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToDate5()
65438 = 02/29/2020

TO_DATE 允许返回对应于一年中某一天的日期表达式。 TO_CHAR 允许返回与日期表达式对应的一年中的哪一天。

儒略历日期(J格式)

SQL中，儒略日可用于1840年12月31日之前的任何日期。因为在内部将这个日期表示为0，所以需要特殊的语法来表示更早的日期。为此，TO_DATE提供了“J”(或“J”)格式。儒略日转换将七位数的内部数值(儒略日计数)转换为显示格式或ODBC格式的日期。例如:

/// d ##class(PHA.TEST.SQLFunction).ToDate6()
ClassMethod ToDate6()
{
	n SQLCODE
	&sql(
		SELECT TO_DATE(2300000,'J')
		INTO :a
	)
	if SQLCODE = 0 {
		w a 	
	} else { 
		w "error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToDate6()
1585-01-31

返回以下日期:1585–01–31(ODBC格式)或01/31/1585(显示格式)。儒略日计数1721424返回公元1年的1月1日(1–01–01)。儒略日计数，如1709980(亚克兴战役标志着罗马帝国在奥古斯都·凯撒统治下的开始)返回公元前(BC)日期，显示的年份前面有一个负号。

儒略日计数在内部总是表示为一个七位数，必要时带有前导零。TO_DATE允许输入不带前导零的儒略日计数。最大允许儒略日是5373484，它返回12/31/9999。允许的最小儒略日是0000001，它返回01/01/-4712(即BCE日期01/01/-4713)。任何超出此范围的值都会生成SQLCODE -400错误，其%msg值为“无效的儒略日值”。儒略日必须介于1和5373484之间。

注意:以下注意事项不应影响使用TO_CHAR和TO_DATE的日期和儒略日计数的相互转换。这可能会影响使用儒略日计数进行的一些计算。

1721424 (1/1/1) 之前的儒略日计数与其他软件实现兼容，例如 Oracle。它们与通常使用的 BCE 日期不同。正常使用中，没有Year 0；日期从 12/31/-1 到 1/1/1。在 Oracle 使用中，儒略日期 1721058 到 1721423 完全无效，并返回错误。这些儒略日期返回不存在的第 0 年作为占位符。因此，涉及 BCE 日期的计算必须调整一年以对应于常见用法。

另请注意，这些日期计数并未考虑公历改革（1582 年 10 月 15 日颁布，但直到 1752 年才在英国及其殖民地采用）引起的日期变化。

TO_DATE 允许返回对应于儒略日计数的日期表达式。 TO_CHAR 允许返回对应于日期表达式的儒略日计数，如以下示例所示：

SELECT 
  TO_CHAR('1776-07-04','J') AS JulianCount,
  TO_DATE(2369916,'J') AS JulianDate
  
  
2369916	1776/7/4 0:00:00

示例

默认日期格式示例

以下嵌入式 SQL 示例指定使用默认日期格式解析的日期字符串。这两个都转换为 DATE 数据类型内部值 60537：

/// d ##class(PHA.TEST.SQLFunction).ToDate7()
ClassMethod ToDate7()
{
	NEW SQLCODE
	&sql(
		SELECT 
			TO_DATE('29 September 2018'),
			TO_DATE('29 SEP 2018')
		INTO :a,:b
	)
	IF SQLCODE=0 {WRITE a,!,b }
	ELSE { WRITE "error:",SQLCODE }
}

以下嵌入式 SQL 示例指定具有两位数年份的日期字符串，格式为默认值。请注意，两位数年份默认为 1900 到 1999。因此，内部 DATE 值为 24012：

/// d ##class(PHA.TEST.SQLFunction).ToDate8()
ClassMethod ToDate8()
{
	NEW SQLCODE
	&sql(
		SELECT 
			TO_DATE('29 September 06'),
			TO_DATE('29 SEP 06')
		INTO :a,:b
	)
	IF SQLCODE=0 {WRITE a,!,b }
	ELSE { WRITE "error:",SQLCODE }
}

指定日期格式示例

以下嵌入式 SQL 示例以各种格式指定日期字符串。所有这些都转换为 DATE 数据类型内部值 64701。

/// d ##class(PHA.TEST.SQLFunction).ToDate9()
ClassMethod ToDate9()
{
	NEW SQLCODE
	&sql(
		SELECT 
			TO_DATE('2018 Feb 22','YYYY MON DD'),
			TO_DATE('FEBRUARY 22, 2018','month dd, YYYY'),
			TO_DATE('2018***02***22','YYYY***MM***DD'),
			TO_DATE('02/22/2018','MM/DD/YYYY')
		INTO :a,:b,:c,:d
	)
	if SQLCODE = 0 {
		w !,a,!,b,!,c,!,d 	
	} else { 
		w "error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToDate9()
 
64701
64701
64701
64701

以下嵌入式 SQL 示例指定不需要元素分隔符的日期格式。它们返回 64701 的日期内部值：

/// d ##class(PHA.TEST.SQLFunction).ToDate10()
ClassMethod ToDate10()
{
	NEW SQLCODE
	&sql(
		SELECT 
			TO_DATE('02222018','MMDDYYYY'),
			TO_DATE('22022018','DDMMYYYY'),
			TO_DATE('20182202','YYYYDDMM'),
			TO_DATE('20180222','YYYYMMDD')
		INTO :a,:b,:c,:d
	)
	if SQLCODE = 0 {
		w !,a,!,b,!,c,!,d 	
	} else { 
		w "error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToDate10()
 
64701
64701
64701
64701

以下示例指定 YYYYMM 日期格式。它不需要元素分隔符。它为缺失的日期元素提供 01，返回日期 64800（2018 年 6 月 1 日）：

/// d ##class(PHA.TEST.SQLFunction).ToDate11()
ClassMethod ToDate11()
{
	NEW SQLCODE
	&sql(
		SELECT TO_DATE('201806','YYYYMM')
		INTO :a 
	)
	if SQLCODE = 0 {
		w a," = ",$ZDATE(a,1,,4) 
	} else { 
		w "error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToDate11()
64800 = 06/01/2018

第143章 SQL函数 TO_DATE（一）

将格式化字符串转换为日期的日期函数。

大纲

TO_DATE(date_string[,format])

TODATE(date_string[,format])

参数

• date_string - 要转换为日期的字符串。基础数据类型为 CHAR 或 VARCHAR2 的字符串日期表达式。
• format - 可选 — 对应于 date_string 的日期格式字符串。如果省略格式，DD MON YYYY& 是默认值；此默认值是可配置的。

描述

名称 TO_DATE 和 TODATE 是可互换的，并且支持 Oracle 兼容性。

TO_DATE 函数将各种格式的日期字符串转换为日期整数值，数据类型为 DATE。它用于输入各种字符串格式的日期，并将它们存储在标准的内部表示中。 TO_DATE 返回具有以下格式的日期：

nnnnn

其中 nnnnn 是介于 0（1840 年 12 月 31 日）和 2980013（9999 年 12 月 31 日）之间的正整数，包括端点。这表示天数。时间值被忽略。 1840 年 12 月 31 日之前的日期可以使用儒略日期来表示，如下所述。

也可以使用 TODATE() 方法调用从 ObjectScript 调用此函数：

$SYSTEM.SQL.Functions.TODATE(date_string,format)

在为字段提供默认值时，可以在数据定义中使用 TO_DATE 函数。例如：

CREATE TABLE mytest
(ID NUMBER(12,0) NOT NULL,
End_Year DATE DEFAULT TO_DATE('12-31-2018','MM-DD-YYYY') NOT NULL)

相关 SQL 函数

• TO_DATE 将格式化的日期字符串转换为日期整数。
• TO_CHAR 执行相反的操作；它将日期整数转换为格式化的日期字符串。
• TO_TIMESTAMP 将格式化的日期和时间字符串转换为标准时间戳。
• CAST 和 CONVERT执行DATE` 数据类型转换。

日期字符串

第一个参数指定一个日期字符串文字。可以为输入 date_string 提供任何类型的日期字符串。每个字符都必须对应于格式字符串，但以下情况除外：

• 可以包含或省略前导零（不带分隔符的 date_string 除外）。
• 年份可以用两位数或四位数字指定。
• 月份名称可以完整指定，也可以指定为名称的前三个字母。只有前三个字母必须是正确的。月份名称不区分大小写。
• 附加到日期的时间值将被忽略。

格式化

第二个参数将日期格式指定为一串代码字符。

默认日期格式

如果未指定格式，TO_DATE 将使用默认格式解析日期字符串。默认格式为 DD MON YYYY。例如，'22 Feb 2018'。

此默认格式可在系统范围内配置，可使用以下任一方式：

$SYSTEM.SQL.Util.SetOption("ToDateDefaultFormat") 

Management Portal TO_DATE 默认格式配置选项。从系统管理中，选择配置，然后选择 SQL 和对象设置，然后选择 SQL。可以查看和设置 TO_DATE 默认格式选项。

要确定当前设置，请调用 $SYSTEM.SQL.CurrentSettings()，它会显示 TO_DATE() 默认格式设置。

格式元素

格式是根据以下规则指定的一个或多个格式元素的字符串：

• 格式元素不区分大小写。
• 几乎任何顺序或数量的格式元素都是允许的。
• 格式字符串使用与 date_string 中的分隔符匹配的非字母数字分隔符（例如，空格、斜杠或连字符）分隔它们的元素。指定日期分隔符的这种使用不依赖于为您的 NLS 语言环境定义的 DateSeparator。
• 以下日期格式字符串不需要分隔符：MMDDYYYY、DDMMYYYY、YYYYMMDD 和 YYYYDDMM。还支持不完整的日期格式 YYYYMM，并假定 DD 值为 01。请注意，在这些情况下，必须为 MM 和 DD 值提供前导零。

下表列出了 format 参数的有效日期格式元素：

TO_DATE 格式还可以包含 D（星期几号）、DY（星期几缩写）或 DAY（星期几名称）元素。但是，这些格式元素未经过验证或用于确定返回值。

单个日期元素的日期格式

可以将 DD、DDD、MM 或 YYYY 指定为完整的日期格式。因为这些格式字符串省略了月份、年份或同时省略了月份和年份， 将它们解释为指的是当前月份和年份：

• DD 返回当前年份当前月份中指定日期的日期。
• DDD 返回当前年份中指定日期的日期。
• MM 返回当前年份中指定月份的第一天的日期。
• YYYY - 返回指定年份当前月份第一天的日期。

以下嵌入式 SQL 示例显示了这些格式：

/// d ##class(PHA.TEST.SQLFunction).ToDate()
ClassMethod ToDate()
{
	NEW SQLCODE
	&sql(
		SELECT
			TO_DATE('300','DDD'),
			TO_DATE('24','DD')
		INTO 
			:a,:b
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w "DDD format: ",a," = ",$ZDATE(a,1,,4),!
		w "DD format: ",b," = ",$ZDATE(b,1,,4) 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToDate()
DDD format: 66409 = 10/27/2022
DD format: 66253 = 05/24/2022

/// d ##class(PHA.TEST.SQLFunction).ToDate1()
ClassMethod ToDate1()
{
	NEW SQLCODE
	&sql(
		SELECT
			TO_DATE('8','MM'),
			TO_DATE('2018','YYYY')
		INTO 
			:a,:b
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w "MM format: ",a," = ",$ZDATE(a,1,,4),!
		w "YYYY format: ",b," = ",$ZDATE(b,1,,4),!
		w "done" 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToDate1()
MM format: 66322 = 08/01/2022
YYYY format: 64769 = 05/01/2018
done

两位数年份转换（RR 和 RRRR 格式）

YY 格式通过简单地附加 19 将两位数的年份值转换为四位数字。因此 07 变为 1907，93 变为 1993。

RR 格式提供更灵活的两位数到四位数年份转换。此转换基于当年。如果当前年份在上半世纪（例如，2000 到 2050），则从 00 到 49 的两位数年份扩展到当前世纪的四位数字年份，从 50 到 2 位数年份99 年扩大到上个世纪的四位数年份。如果当前年份在世纪下半叶（例如，2050 年到 2099 年），则所有两位数年份都将扩展为当前世纪中的四位数年份。下面的嵌入式 SQL 示例显示了两位数年份到四位数年份的这种扩展：

/// d ##class(PHA.TEST.SQLFunction).ToDate2()
ClassMethod ToDate2()
{
	NEW SQLCODE
	&sql(
		SELECT 
			TO_DATE('29 September 00','DD MONTH RR'),
			TO_DATE('29 September 18','DD MONTH RR'),
			TO_DATE('29 September 49','DD MONTH RR'),
			TO_DATE('29 September 50','DD MONTH RR'),
			TO_DATE('29 September 77','DD MONTH RR')
		INTO 
			:a,:b,:c,:d,:e
	)
	if SQLCODE = 0 {
		w a," = ",$ZDATE(a,1,,4),!
		w b," = ",$ZDATE(b,1,,4),!
		w c," = ",$ZDATE(c,1,,4),!
		w d," = ",$ZDATE(d,1,,4),!
		w e," = ",$ZDATE(e,1,,4) 
	} else { 
		w "error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToDate2()
58346 = 09/29/2000
64920 = 09/29/2018
76243 = 09/29/2049
40083 = 09/29/1950
49945 = 09/29/1977

RRRR 格式允许输入两位数和四位数字的混合年份。四位数年份不变（与 YYYY 相同）。使用 RR 格式算法将两位数年份转换为四位数年份。这显示在以下嵌入式 SQL 示例中：

/// d ##class(PHA.TEST.SQLFunction).ToDate3()
ClassMethod ToDate3()
{
	n SQLCODE
	&sql(
		SELECT 
			TO_DATE('29 September 2018','DD MONTH RRRR'),
			TO_DATE('29 September 18','DD MONTH RRRR'),
			TO_DATE('29 September 1949','DD MONTH RRRR'),
			TO_DATE('29 September 49','DD MONTH RRRR'),
			TO_DATE('29 September 1950','DD MONTH RRRR'),
			TO_DATE('29 September 50','DD MONTH RRRR')
		INTO 
			:a,:b,:c,:d,:e,:f
	)
	if SQLCODE = 0 {
		w a," 4-digit = ",$ZDATE(a,1,,4),!
		w b," 2-digit = ",$ZDATE(b,1,,4),!
		w c," 4-digit = ",$ZDATE(c,1,,4),!
		w d," 2-digit = ",$ZDATE(d,1,,4),!
		w e," 4-digit = ",$ZDATE(e,1,,4),!
		w f," 2-digit = ",$ZDATE(f,1,,4) 
	} else { 
		w "error:",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToDate3()
64920 4-digit = 09/29/2018
64920 2-digit = 09/29/2018
39718 4-digit = 09/29/1949
76243 2-digit = 09/29/2049
40083 4-digit = 09/29/1950
40083 2-digit = 09/29/1950

第142章 SQL函数 TO_CHAR（三）

数字到字符串示例

以下嵌入式 SQL 示例显示了基本的数字到字符串的转换：

/// d ##class(PHA.TEST.SQLFunction).ToChar3()
ClassMethod ToChar3()
{
	&sql(
		SELECT 
			TO_CHAR(1000,'9999'),
			TO_CHAR(10,'9999')
		INTO 
			:numfull,:numshort
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Formatted number:",numfull
		w !,"Formatted number:",numshort
		w !,"Note leading blanks" 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToChar3()
 
Formatted number: 1000
Formatted number:   10
Note leading blank

返回具有适当数量的前导空格的指定数字。无符号正数前面总是有一个空白字符。如果指定数字的位数少于格式参数，则提供额外的前导空格。

以下嵌入式 SQL 示例显示了分隔符的使用：

/// d ##class(PHA.TEST.SQLFunction).ToChar4()
ClassMethod ToChar4()
{
	&sql(
		SELECT 
			TO_CHAR(1000,'9,999.99'),
			TO_CHAR(1000,'9G999D99')
		INTO 
			:comma,:groupsep
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Formatted number:",comma
		w !,"Formatted number:",groupsep
		w !,"Note leading blank" 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToChar4()
 
Formatted number: 1,000.00
Formatted number: 1,000.00
Note leading blank

第一个 TO_CHAR 返回字符串'1,000.00';。第二个 TO_CHAR 也可能返回此值，但显示的分隔符取决于区域设置。

以下嵌入式 SQL 示例显示了正号和负号的使用：

/// d ##class(PHA.TEST.SQLFunction).ToChar5()
ClassMethod ToChar5()
{
	&sql(
		SELECT 
			TO_CHAR(10,'99.99'),
			TO_CHAR(-10,'99.99'),
			TO_CHAR(10,'S99.99'),
			TO_CHAR(-10,'S99.99'),
			TO_CHAR(10,'99.99S'),
			TO_CHAR(-10,'99.99S')
		INTO 
			:pos,:neg,:poslead,:neglead,:postrail,:negtrail
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Formatted number:",pos
		w !,"Formatted number:",neg
		w !,"Formatted number:",poslead
		w !,"Formatted number:",neglead
		w !,"Formatted number:",postrail
		w !,"Formatted number:",negtrail
		w !,"Note use of leading blank" 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToChar5()
 
Formatted number: 10.00
Formatted number:-10.00
Formatted number:+10.00
Formatted number:-10.00
Formatted number:10.00+
Formatted number:10.00-
Note use of leading blank

请注意，前导空格仅出现在没有符号格式的正数之前。无论符号的位置如何，负数或任何带符号的数字之前都不会出现前导空格。

以下嵌入式 SQL 示例显示了使用“FM”格式覆盖无符号正数的默认前导空白：

/// d ##class(PHA.TEST.SQLFunction).ToChar6()
ClassMethod ToChar6()
{
	&sql(
		SELECT 
			TO_CHAR(12345678.90,'99,999,999.99'),
			TO_CHAR(12345678.90,'FM99,999,999.99')
		INTO 
			:num,:fmnum
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Formatted number:",num
		w !,"Formatted number:",fmnum
		w !,"Note leading blank" 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToChar6()
 
Formatted number: 12,345,678.90
Formatted number:12,345,678.90
Note leading blank

以下嵌入式 SQL 示例显示了前导美元符号的使用：

/// d ##class(PHA.TEST.SQLFunction).ToChar7()
ClassMethod ToChar7()
{
	&sql(
		SELECT 
			TO_CHAR(1234567890,'$9G999G999G999'),
			TO_CHAR(1234567890,'S$9G999G999G999'),
			TO_CHAR(12345678.90,'$99G999G999D99')
		INTO 
			:d,:sd,:dD
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Formatted number:",d
		w !,"Formatted number:",sd
		w !,"Formatted number:",dD
		w !,"Note leading blanks" 
	}
}

DHC-APP> d ##class(PHA.TEST.SQLFunction).ToChar7()
 
Formatted number: $1,234,567,890
Formatted number:+$1,234,567,890
Formatted number: $12,345,678.90
Note leading blanks

美元符号前面总是有一个符号或一个空白字符。

以下嵌入式 SQL 示例显示了当 format 参数包含的整数位数少于输入数值时会发生什么：

/// d ##class(PHA.TEST.SQLFunction).ToChar8()
ClassMethod ToChar8()
{
	&sql(
		SELECT 
			TO_CHAR(1234567.89,'9'),
			TO_CHAR(1234567.89,'99'),
			TO_CHAR(1234567.89,'99D99')
		INTO :a,:b,:c
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Formatted number:",a
		w !,"Formatted number:",b
		w !,"Formatted number:",c 
	}
}

每个 TO_CHAR 分别返回一串井号：“##”、“###”和“######”。

DHC-APP> d ##class(PHA.TEST.SQLFunction).ToChar8()
 
Formatted number:##
Formatted number:###
Formatted number:######

以下嵌入式 SQL 示例显示了当 format 参数包含的十进制（小数）位数少于输入数值表达式时会发生什么：

/// d ##class(PHA.TEST.SQLFunction).ToChar9()
ClassMethod ToChar9()
{
	&sql(
		SELECT 
			TO_CHAR(1234567.4999,'9999999.9'),
			TO_CHAR(1234567.91,'9999999')
		INTO :a,:b
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Formatted number:",a
		w !,"Formatted number:",b
	}
}

返回的数字分别四舍五入为“1234567.5”和“1234568”。

DHC-APP> d ##class(PHA.TEST.SQLFunction).ToChar9()
 
Formatted number: 1234567.5
Formatted number: 1234568

第141章 SQL函数 TO_CHAR（二）

可以使用 TO_CHAR 将以下 tochar 表达式时间值转换为格式化的时间字符串：

• $HOROLOG 时间整数（$HOROLOG 的时间部分）。 tochar-expression 的值必须是有效的逻辑时间（0 到 86399 范围内的整数）。不要提供包含日期和时间组件的完整 $HOROLOG 值（例如 64701,42152）； TO_CHAR 时间转换会错误地将 $HOROLOG 的第一个（日期）组件转换为格式化的时间字符串，并忽略第二个（时间）组件。
• 逻辑时间戳值。 tochar-expression 的值必须是 %TimeStamp 数据类型（不是字符串数据类型），格式为 YYYY-MM-DDhh:mm:ss。时间戳的日期部分被忽略，时间部分被转换。例如，SYSDATE 是一个逻辑时间戳。
• 标准 ODBC 时间格式的时间值。 tochar-expression 的值必须采用 hh:mm:ss 格式，并且可以是字符串。
• 本地时间格式的时间值（使用当前 NLS 区域设置）。例如，如果 NLS TimeSeparator 设置为“^”，则 tochar-expression 的值可以采用 hh^mm^ss 格式，并且可以是字符串。

在所有这些情况下，格式的值必须是一个只包含时间格式代码的字符串：

包含任何其他格式代码值会导致 tochar-expression 整数被解释为日期。

以下示例导致“64701”被解释为时间值 05:58:21 PM：

SELECT TO_CHAR('64701','HH12:MI:SS PM')


05:58:21 PM

以下示例将两个逻辑时间戳的时间部分转换为格式化的时间字符串。请注意，格式不支持小数秒； tochar 表达式中的小数秒被截断。

SELECT TO_CHAR(SYSDATE,'HH12:MI:SS PM'),
       TO_CHAR(CURRENT_TIMESTAMP(6),'HH12:MI:SS PM')
       
11:18:03 AM	11:18:03 AM

以下嵌入式 SQL 示例转换以 ODBC 标准格式和当前 NLS 语言环境格式指定的时间值：

/// d ##class(PHA.TEST.SQLFunction).ToChar2()
ClassMethod ToChar2()
{
	s restore = ##class(%SYS.NLS.Format).GetFormatItem("TimeSeparator")
	w "Time Separator is = ", restore,!
	d ##class(%SYS.NLS.Format).SetFormatItem("TimeSeparator", "^")
	w "Time Separator is now = ",##class(%SYS.NLS.Format).GetFormatItem("TimeSeparator"),!
	&sql(
		SELECT 
			TO_CHAR('15:35:43.99', 'HH12:MI:SS PM'),
			TO_CHAR('15^35^43.99','HH12:MI:SS PM') 
		INTO 
			:standard,:local
	)
	w "Converted standard-format time: ",standard,!
	w "Converted locale-format time: ",local,!
	d ##class(%SYS.NLS.Format).SetFormatItem("TimeSeparator",restore)
	w "Time Separator is = ", ##class(%SYS.NLS.Format).GetFormatItem("TimeSeparator")
}

时间戳到格式化日期时间字符串的转换

可以使用 TO_CHAR 将时间戳转换为格式化的日期时间字符串。 tochar-expression 的值必须是有效的逻辑时间戳值。

时间戳的日期部分使用日期到字符串的转换格式代码进行格式化。下表列出了时间戳的时间部分的其他格式代码。

以下示例返回当前系统日期（时间戳），以及转换为以两种不同格式显示的当前系统日期：

SELECT SYSDATE,
       TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MI:SS'),
       TO_CHAR(SYSDATE,'DD MONTH YYYY at SSSSS seconds')

请注意，格式字符串中使用的任何不是格式代码的字符都会在结果字符串中原位返回。

数字到字符串的转换

可以使用 TO_CHAR 将数字转换为格式化的数字字符串。下表列出了使用 TO_CHAR 的格式参数的有效格式代码。

如果省略格式参数，则输入数值被评估为整数：前导零和前导加号被删除，前导减号被保留，并且数值在第一个非数字字符处被截断，例如逗号或期间。没有提供前导空格或其他格式。

格式可以将小数分隔符和数字组分隔符指定为文字字符，也可以指定为区域设置的 DecimalSeparator 和 NumericGroupSeparator 的当前值。可以按如下方式确定当前的语言环境值：

DHC-APP>  WRITE ##class(%SYS.NLS.Format).GetFormatItem("DecimalSeparator"),!
.
 
DHC-APP>  WRITE ##class(%SYS.NLS.Format).GetFormatItem("NumericGroupSeparator")  
,

如果格式参数包含的整数位数少于输入数值表达式，则 TO_CHAR 不返回数字；相反，它返回一个由两个或多个井号 (##) 组成的字符串。井号的数量表示当前格式参数的长度加一。

如果格式参数包含的小数位数少于输入数值表达式，则 TO_CHAR 将数字四舍五入为指定的小数位数，如果未提供十进制格式，则四舍五入为整数。

如果 tochar-expression 为 null，则 TO_CHAR 返回 null。

第140章 SQL函数 TO_CHAR（一）

将日期、时间戳或数字转换为格式化字符串的字符串函数。

大纲

TO_CHAR(tochar-expression[,format])

TOCHAR(tochar-expression[,format])

参数

• tochar-expression - 要转换的逻辑日期、时间戳或数字表达式。
• format - 可选 — 为 tochar 表达式转换指定日期、时间戳或数字格式的字符代码。如果省略，TO_CHAR 将 tochar-expression 作为规范数字返回。

描述

名称 TO_CHAR 和 TOCHAR 是可互换的，并且支持 Oracle 兼容性。

带格式的 TO_CHAR 函数有五种用途：

• 将日期整数转换为格式化的日期字符串。

• 日期在1840年之前转换为朱利安日期int

• 时间的整数转换为格式化时间生态

• 将日期和时间格式化的datetime

• 将数字转换成一个格式化的数字字符串。

这个函数也可以从ObjectScrip调用

$SYSTEM.SQL.Functions.TOCHAR(tochar-expression,format)

有效和无效的参数

• 要将 tochar-expression 解释为时间戳，它的格式必须为 YYYY-MM-DD HH:MI:SS，或以下有效变体之一： 小于 10 的月份和日期值可以包含或省略前导零；如果前导零被省略，则在返回的日期中也会被省略。可以省略秒值，但必须指定指示其位置的冒号 (HH:MI:)；在返回的时间中，秒默认为 00。秒值可能包括小数秒 (HH:MM:SS.nnn)；在返回的时间内，这些小数秒被截断。时间戳必须包含时间部分，即使格式未指定时间格式。

• 如果 tochar-expression 不是有效的时间戳格式，则 TO_CHAR 将其解释为整数，并在遇到第一个非整数字符时结束解释。如果 format 是日期或时间戳格式，则 TO_CHAR 将 tochar-expression 解释为 $HOROLOG 日期整数。因此 2010-03-23 12-15:23（注意时间值中的错误连字符）被解释为 $HOROLOG 日期 2010（1846-07-03 12:00:00 AM）。

• 如果 tochar 表达式日期或时间不是有效的日期或时间值， 会发出 SQLCODE -400 错误。这可能发生在不存在的日期（例如 2 月 30 日）或 1840 年 12 月 31 日之前的日期。

• 如果指定的格式包含无效的日期、时间或时间戳代码元素（例如 YYYYY、MIN、HH48），则 TO_CHAR 返回无效代码元素的格式代码文字；它返回有效代码元素的日期、时间或时间戳转换值（如果有）。

• 如果 TO_CHAR 无法识别任何格式代码元素（例如，格式是空字符串）或数字格式的位数少于 tochar 表达式值，则 TO_CHAR 返回井号 (#) 字符。 （当 tochar-expression 以至少两个整数数字开头时为真；否则 TO_CHAR 返回 NULL。）

• 如果省略格式，TO_CHAR 将 tochar-expression 的数字部分作为规范数字返回，遇到非数字字符时截断。如果 tochar-expression 为非数字，则 TO_CHAR 返回 0。如果 tochar-expression 为 null，则 TO_CHAR 返回 null。

TO_CHAR 和 TO_DATE

TO_CHAR 将日期整数转换为格式化的日期字符串，或将时间整数转换为格式化的时间字符串。如果错误地为 TO_CHAR 提供了格式化的日期或时间字符串，它会返回错误的数据。

TO_DATE 将格式化的日期字符串转换为相应的日期整数。如果错误地为 TO_DATE 提供了日期整数，它会返回未修改的整数。

以下示例显示了 TO_DATE 和 TO_CHAR 的这些正确和错误用法。

以下嵌入式 SQL 示例使用 TO_DATE执行日期转换。 TO_DATE 接受一个日期字符串并返回相应的日期整数 (59832)。 $ZDATE 函数用于将此日期整数显示为格式化日期 02/22/2018。在这个例子中，TO_DATE 也被错误地提供了一个日期整数；它只是返回这个整数。

/// d ##class(PHA.TEST.SQLFunction).ToChar()
ClassMethod ToChar()
{
	&sql(
		SELECT 
			TO_DATE('2018-02-22','YYYY-MM-DD'), /* correct */
			TO_DATE(64701,'YYYY-MM-DD')         /* ERROR!  */
		INTO 
			:a,:b
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,a
		w !,$ZDATE(a)
		w !,b
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToChar()
 
64701
02/22/2018
64701

以下嵌入式 SQL 示例显示了使用 TO_CHAR 的日期转换。如预期的那样，第一个 TO_CHAR 将日期整数转换为相应的格式化日期字符串。但是，第二个 TO_CHAR 给出了意想不到的结果。由于 TO_CHAR 需要数字输入，因此它将输入中的日期分隔符视为减号并执行减法。因此，它格式化与日期整数 1970（2004 减 10 减 24）相对应的日期：1846–5–24。显然，这不是程序员的本意。

/// d ##class(PHA.TEST.SQLFunction).ToChar1()
ClassMethod ToChar1()
{
	&sql(
		SELECT 
			TO_CHAR(59832,'YYYY-MM-DD'),     /* correct */
			TO_CHAR(2004-10-24,'YYYY-MM-DD') /* ERROR!  */
		INTO 
			:a,:b
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,a
		w !,b 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).ToChar1()
 
2004-10-24
1846-05-24

相关 SQL 函数

• TO_CHAR 将日期整数、时间戳或数字转换为字符串。
• TO_DATE 对日期执行反向操作；它将格式化的日期字符串转换为日期整数。
• TO_TIMESTAMP 对时间戳执行反向操作；它将格式化的日期和时间字符串转换为标准时间戳。
• TO_NUMBER 对数字执行相反的操作；它将数字字符串转换为数字。
• CAST 和 CONVERT 执行 DATE、TIMESTAMP和 NUMBER 数据类型转换。

日期到字符串的转换

$HOROLOG 格式是用于表示日期和时间的SQL 逻辑格式。它是一个包含两个逗号分隔整数的字符串：第一个是自 1840 年 12 月 31 日以来的天数；第二个是自当天午夜以来的秒数。

可以使用 TO_CHAR 将 $HOROLOG 日期整数或两个逗号分隔整数的 $HOROLOG 字符串转换为格式化的日期字符串或格式化的日期和时间字符串。 tochar-expression 的值必须是有效的 $HOROLOG 值。

下表列出了此版本 TO_CHAR 的有效日期格式代码。

日期格式元素之间需要分隔符，但以下格式字符串除外：YYYYMMDD、DDMMYYYY 和 YYYYMM。最后一个返回年份和月份值并忽略月份中的日期。

请注意，格式代码定义中提到的语言环境指的是 ObjectScript $ZDATE 和 $ZDATEH 文档中描述的相同语言环境。

日期转换示例

以下是 TO_CHAR 与 $HOROLOG 日期整数或完整的 $HOROLOG 字符串值的所有有效用法，以返回格式化的日期字符串或日期和时间字符串：

SELECT TO_CHAR(64701,'YYYY-MM-DD') AS DateFD,
       TO_CHAR(64701,'YYYY-MM-DD HH24:MI:SS') AS DateFDT,
       TO_CHAR('64701,50278','YYYY-MM-DD') AS DateTimeFD,
       TO_CHAR('64701,50278','YYYY-MM-DD HH24:MI:SS') AS DateTimeFDT

在以下示例中，每个 TO_CHAR 接受一个日期整数并返回一个根据格式字符串参数格式化的日期字符串：

SELECT TO_CHAR(64701,'MM/DD/YYYY'),         /* returns 02/22/2018            */
       TO_CHAR(64701,'DAY MONTH DD, YYYY')  /* returns Thursday February 22, 2018 */

以下示例采用日期整数并返回格式化的日期字符串。不是格式字符的字符作为文字传递给输出字符串：

SELECT TO_CHAR(64701,'The date MM/DD/YYYY should be noted')

The date 02/22/2018 should be noted

一年中的一天

可以使用 DDD 将日期表达式转换为一年中的某天（自 1 月 1 日以来经过的天数）和年份。格式字符串 DDD,YYYY 必须与 $HOROLOG 格式的日期表达式配对。 （$HOROLOG 时间值，如果指定，将被忽略。） DDD 和 YYYY（或 YY）格式元素可以按任何顺序指定；它们之间的分隔符是强制性的，并作为文字返回。以下示例显示了一年中的这一天的用法：

SELECT TO_CHAR('64701','YYYY:DDD')

2018:053

SELECT TO_CHAR('64701,12345','DDD YY')

053 18

TO_CHAR 允许返回与日期表达式对应的一年中的哪一天。 TO_DATE 允许返回对应于一年中某一天的日期表达式。

儒略日期转换

儒略日（Julian day，JD）是指由公元前4713年1月1日，协调世界时中午12时开始所经过的天数，多为天文学家采用，用以作为天文学的单一历法，把不同历法的年表统一起来。如果计算相隔若干年的两个日期之间间隔的天数，利用儒略日就比较方便。

提供“儒略”日期格式以允许 1841 年之前的日期。TO_CHAR 将数据类型 %Date 或 %TimeStamp 的日期值转换为七位儒略日期整数。

胡子怡： 默认情况下，%Date 数据类型不表示 1840 年 12 月 31 日之前的日期。但是，可以重新定义此数据类型的 MINVAL 参数以允许将较早的日期表示为负整数，限制为第 1 年 1 月 1 日.

如果指定的格式由包含字母“J”的字符串组成，则返回的日期值将是“儒略”日期，即从公元前 4712 年 1 月 1 日开始的天数。格式字符串中只能指定字母“J”；包含任何其他字符会导致将“J”视为文字，并将日期翻译为标准日期。

儒略日期的最大 tochar 表达式值为“9999-12-31”，对应儒略日计数 5373484。最小值为“-4712-01-01”，对应儒略日计数 0000001。儒略日计数为始终表示为七位整数，必要时带有前导零。

以下示例返回 2369916（签署美国独立宣言）和 1709980（亚克兴战役标志着奥古斯都凯撒统治下罗马帝国的开始）：

SELECT TO_CHAR('1776-07-04','J') AS UnitedStatesStart,
       TO_CHAR('-0031-09-02','J') AS RomanEmpireStart
       
2369916	1709980

注意：以下注意事项不应影响使用 TO_CHAR 和 TO_DATE 的日期和儒略日计数的相互转换。它可能会影响使用儒略日计数进行的某些计算。 1721424 (1/1/1) 之前的儒略日计数与其他软件实现兼容，例如 Oracle。它们与通常使用的 BCE 日期不同。正常使用中，没有Year 0；日期从 12/31/-1 到 1/1/1。在 Oracle 使用中，儒略日期 1721058 到 1721423 完全无效，并返回错误。这些儒略日期返回不存在的第 0 年作为占位符。因此，涉及 BCE 日期的计算必须调整一年以对应于常见用法。 另请注意，这些日期计数并未考虑公历改革（1582 年 10 月 15 日颁布，但直到 1752 年才在英国及其殖民地采用）引起的日期变化。

TO_CHAR 允许返回对应于日期表达式的儒略日计数。 TO_DATE 允许返回对应于儒略日计数的日期表达式，如以下示例所示：

SELECT TO_CHAR('1776-07-04','J') AS JulianCount,
      TO_DATE(2369916,'J') AS JulianDate
      
      
2369916	1776/7/4 0:00:00

第139章 SQL函数 TIMESTAMPDIFF

一个标量日期/时间函数，它返回指定日期部分的两个时间戳之间差异的整数计数。

大纲

{fn TIMESTAMPDIFF(interval-type,startdate,enddate)}

参数

• interval-type - 返回值将表示的时间/日期间隔类型。
• startdate - 时间戳值表达式。
• enddate - 将与 startdate 进行比较的时间戳值表达式。

描述

TIMESTAMPDIFF 函数返回指定日期部分间隔（秒、天、周等）的两个给定时间戳之间的差异（即，从另一个中减去一个时间戳）。返回的值是一个 INTEGER，即两个时间戳之间的这些间隔数。 （如果 enddate 早于 startdate，则 TIMESTAMPDIFF 返回负 INTEGER 值。）

开始日期和结束日期是时间戳。这些时间戳可以是 %Library.TimeStamp 数据类型格式 (yyyy-mm-dd hh:mm:ss.ffff) 或 %Library.PosixTime 数据类型格式（编码的 64 位有符号整数）。

间隔类型参数可以是以下时间戳间隔之一：

• SQL_TSI_FRAC_SECOND

• SQL_TSI_SECOND

• SQL_TSI_MINUTE

• SQL_TSI_HOUR

• SQL_TSI_DAY

• SQL_TSI_WEEK

• SQL_TSI_MONTH

• SQL_TSI_YEAR

这些时间戳间隔可以使用单引号或双引号来指定，带或不带引号。它们不区分大小写。

TIMESTAMPDIFF 和 DATEDIFF 不处理季度quarters （3 个月间隔）。

请注意，TIMESTAMPDIFF 只能用作 ODBC 标量函数（使用大括号语法）。可以使用 DATEDIFF 通用函数对时间戳执行类似的时间/日期比较操作。

%TimeStamp 格式化

如果 startdate 或 enddate 参数采用 %Library.TimeStamp 数据类型格式 (yyyy-mm-dd hh:mm:ss.ffff)，则适用以下规则：

• 如果任一时间戳表达式仅指定时间值并且间隔类型指定日期间隔（天、周、月或年），则在计算结果间隔计数之前，时间戳的缺失日期部分默认为“1900–01–01” .
• 如果任一时间戳表达式仅指定日期值并且间隔类型指定时间间隔（小时、分钟、秒、小数秒），则在计算结果间隔计数之前，时间戳的缺失时间部分默认为“00:00:00.000” .
• 可以包含或省略任意位数精度的小数秒。 SQL_TSI_FRAC_SECOND 以千分之一秒的整数计数形式返回小数秒的差异（精度为三位数）。 %PosixTime 值始终包含六位精度。

范围和值检查

TIMESTAMPDIFF 对输入值执行以下检查。

• startdate 和 enddate 的所有指定部分必须有效，然后才能执行任何 TIMESTAMPDIFF 操作。
• 日期字符串必须完整且格式正确，其中包含适当数量的元素和每个元素的数字，以及适当的分隔符。年份必须指定为四位数。无效的日期值会导致 SQLCODE -8 错误。
• 日期值必须在有效范围内。年：0001 到 9999。月：1 到 12。日：1 到 31。小时：00 到 23。分：0 到 59。秒：0 到 59。一个月中的天数必须与月和年相匹配。例如，日期“02–29”仅在指定年份是闰年时有效。无效的日期值会导致 SQLCODE -8 错误。
• 小于 10（月和日）的日期值可能包括或省略前导零。不允许使用其他非规范整数值。因此，Day 值“07”或“7”有效，但“007”、“7.0”或“7a”无效。
• 时间值可以全部或部分省略。如果 startdate 或 enddate 指定了不完整的时间，则为未指定的部分提供零。
• 小于 10 的小时值必须包含前导零。省略此前导零会导致 SQLCODE -8 错误。

示例

以下示例返回 7，因为第二个时间戳 (2017-12-20 12:00:00) 比第一个大 7 个月：

SELECT {fn TIMESTAMPDIFF(SQL_TSI_MONTH,
     '2017-5-19 00:00:00','2017-12-20 12:00:00')}
     
     
7

以下示例返回 566，因为第二个时间戳 ('12:00:00')) 比第一个时间戳 (02:34:12) 大 566 分钟：

SELECT {fn TIMESTAMPDIFF(SQL_TSI_MINUTE,'02:34:12','12:00:00')}


566

以下示例返回 -1440，因为第二个时间戳比第一个小一天（1440 分钟）：

SELECT {fn TIMESTAMPDIFF(SQL_TSI_MINUTE,'2017-12-06','2017-12-05')}

-1440

第138章 SQL函数 TIMESTAMPADD

一个标量日期/时间函数，它返回一个新的时间戳，该时间戳是通过将指定日期部分的多个间隔添加到时间戳来计算的。

大纲

{fn TIMESTAMPADD(interval-type,integer-exp,timestamp-exp)}

参数

• interval-type - integer-exp 表示的时间/日期间隔类型，指定为关键字。
• integer-exp - 要添加到 timestamp-exp 的整数值表达式。
• timestamp-exp - 时间戳值表达式，将增加 integer-exp 的值。

描述

TIMESTAMPADD 函数通过将指定的日期部分增加指定的单位数来修改日期/时间表达式。例如，如果 interval-type 是 SQL_TSI_MONTH 并且 integer-exp 是 5，则 TIMESTAMPADD 将 timestamp-exp 增加五个月。还可以通过为 integer-exp 指定负整数来减少日期部分。

TIMESTAMPADD 返回与输入timestamp-exp 数据类型相同的时间戳。此时间戳可以是 %Library.TimeStamp 数据类型格式 (yyyy-mm-dd hh:mm:ss.ffff) 或 %Library.PosixTime 数据类型格式（编码的 64 位有符号整数）。

请注意，TIMESTAMPADD 只能用作 ODBC 标量函数（使用大括号语法）。

可以使用 DATEADD 通用函数对时间戳执行类似的时间/日期修改操作。

Interval Types

间隔类型参数可以是以下时间戳间隔之一：

• SQL_TSI_FRAC_SECOND
• SQL_TSI_SECOND
• SQL_TSI_MINUTE
• SQL_TSI_HOUR
• SQL_TSI_DAY
• SQL_TSI_WEEK
• SQL_TSI_MONTH
• SQL_TSI_QUARTER
• SQL_TSI_YEAR

这些时间戳间隔可以使用单引号或双引号来指定，带或不带引号。它们不区分大小写。

增加或减少时间戳间隔会导致其他间隔被适当地修改。例如，增加午夜过后的小时数会自动增加天数，而这又会增加月数，依此类推。 TIMESTAMPADD 总是返回一个有效的日期，考虑到一个月的天数，并计算闰年。例如，将 1 月 31 日增加一个月会返回 2 月 28 日（该月中的最高有效日期），除非指定的年份是闰年，在这种情况下它会返回 2 月 29 日。

可以按三位精度的小数秒递增或递减。将小数秒指定为千分之一秒的整数计数（001 到 999）。

DATEADD 和 TIMESTAMPADD 处理季度（3 个月间隔）； DATEDIFF 和 TIMESTAMPDIFF 不处理宿舍。

%TimeStamp 格式化

如果 timestamp-exp 参数采用 %Library.TimeStamp 数据类型格式 (yyyy-mm-dd hh:mm:ss.ffff)，则适用以下规则：

• 如果 timestamp-exp 仅指定时间值，则在计算生成的时间戳之前，timestamp-exp 的日期部分设置为“1900-01-01”。
• 如果 timestamp-exp 仅指定一个日期值，则在计算结果时间戳之前，timestamp-exp 的时间部分会设置为“00:00:00”。
• timestamp-exp 可以包括或省略小数秒。 timestamp-exp 可以包含任意位数的精度，但间隔类型 SQL_TSI_FRAC_SECOND 指定精确的三位精度。尝试指定小于或大于三位数的 SQL_TSI_FRAC_SECOND 可能会产生不可预知的结果。

范围和值检查

TIMESTAMPADD 对 %Library.TimeStamp 输入值执行以下检查：

• 在执行任何 TIMESTAMPADD 操作之前，timestamp-exp 的所有指定部分都必须有效。
• 日期字符串必须完整且格式正确，其中包含适当数量的元素和每个元素的数字，以及适当的分隔符。年份必须指定为四位数。无效的日期值会导致 SQLCODE -400 错误。
• 日期值必须在有效范围内。年：0001 到 9999。月：1 到 12。日：1 到 31。小时：00 到 23。分：0 到 59。秒：0 到 59。一个月中的天数必须与月和年相匹配。例如，日期“02–29”仅在指定年份是闰年时有效。无效的日期值会导致 SQLCODE -400 错误。
• 返回的递增（或递减）年份值必须在 0001 到 9999 范围内。超出此范围递增或递减返回 。
• 小于 10 的日期值可以包括或省略前导零。不允许使用其他非规范整数值。因此，Day 值“07”或“7”有效，但“007”、“7.0”或“7a”无效。小于 10 的日期值始终以前导零返回。
• 时间值可以全部或部分省略。如果 timestamp-exp 指定了不完整的时间，则为未指定的部分提供零。
• 小于 10 的小时值必须包含前导零。省略此前导零会导致 SQLCODE -400 错误。

示例

以下示例将 1 周添加到原始时间戳：

SELECT {fn TIMESTAMPADD(SQL_TSI_WEEK,1,'2017-12-20 12:00:00')}

以下示例将 5 个月添加到原始时间戳：

SELECT {fn TIMESTAMPADD(SQL_TSI_MONTH,5,'2022-12-20 12:00:00')}

以下示例将原始时间戳增加 45 分钟：

SELECT {fn TIMESTAMPADD(SQL_TSI_MINUTE,45,'2017-12-20 00:00:00')}

以下示例将原始时间戳减少 45 分钟：

SELECT {fn TIMESTAMPADD(SQL_TSI_MINUTE,-45,'2017-12-20 00:00:00')}

第137章 SQL函数 TAN

标量数值函数，返回角度的正切（以弧度为单位）。

大纲

{fn TAN(numeric-expression)}

参数

• numeric-expression - 一个数值表达式。这是一个以弧度表示的角度。

TAN 返回 NUMERIC 或 DOUBLE 数据类型。如果 numeric-expression 是数据类型 DOUBLE，则 TAN 返回 DOUBLE；否则，它返回 NUMERIC。

描述

TAN 接受任何数值并返回其切线。如果传递 NULL 值，TAN 返回 NULL。 TAN 将非数字字符串视为数值 0。

TAN 返回一个精度为 36、比例为 18 的值。

TAN 只能用作 ODBC 标量函数（使用大括号语法）。

可以使用 DEGREES 函数将弧度转换为度数。可以使用 RADIANS 函数将度数转换为弧度。

示例

以下示例显示了 TAN 的效果。

SELECT {fn TAN(0.52)} AS Tangent 


0.572561830251668415

第136章 SQL函数 SUBSTRING

字符串函数，它从任何数据类型的数据（包括流数据）中返回一个子字符串。

大纲

SUBSTRING(string-expression,start[,length])

SUBSTRING(string-expression FROM start [FOR length])

{fn SUBSTRING(string-expression,start[,length])}

参数

• string-expression - 要从中派生子字符串的字符串表达式。表达式，可以是列名、字符串文字或另一个标量函数的结果。字段可以是任何数据类型：字符串（例如 CHAR 或 VARCHAR）、数字或数据类型为 %Stream.GlobalCharacter 或 %Stream.GlobalBinary 的数据流字段。
• start - 一个整数，它指定 string-expression 中开始子字符串的位置。 string-expression 中的第一个字符位于位置 1。如果起始位置高于字符串的长度，则 SUBSTRING 返回一个空字符串 。如果起始位置小于 1（零或负数），则子字符串从位置 1 开始，但子字符串的长度会减少起始位置。
• length - 可选 — 一个整数，指定要返回的子字符串的长度。如果未指定长度，则默认返回字符串的其余部分。

描述

SUBSTRING 接受任何数据类型的数据并将该数据的子字符串作为数据类型 %String 返回。当然，子字符串可以是作为字符串返回的完整数据值。

start 的值控制子字符串的起点：

• 如果 start 为 1，则子字符串从 string-expression 的开头开始。
• 如果 start 大于 1，则子字符串从 string-expression 开头计数的那个字符位置开始。
• 如果 start 小于 1，则子字符串从 string-expression 的开头开始，但 length 的值会减少相应的量。因此，如果 start 为 0，则 length 的值减 1；如果 start 为 –1，则 length 的值减 2。

length 的值控制子字符串的大小：

• 如果 length 为正值（1 或更大），则子字符串在起始位置右侧结束 length 个字符。 （如果起始编号小于 1，此有效长度可能会减少。）
• 如果长度大于字符串中剩余的字符数，则返回从起始位置右侧到字符串表达式结尾的所有字符。
• 如果长度为零，则返回 NULL。
• 如果长度是负数， 会发出 SQLCODE –140 错误。

SUBSTRING 可用作 ODBC 标量函数（使用大括号语法）或 SQL 通用函数。

返回值

如果 string-expression 是任何 %String 数据类型，则 SUBSTRING 返回值是与 string-expression 数据类型相同的数据类型。这允许 SUBSTRING 使用特殊编码处理用户定义的字符串数据类型。

如果 string-expression 不是 %String 数据类型（例如，%Stream.GlobalCharacter），则 SUBSTRING 返回值为 %String。

如果任何 SUBSTRING 参数值为 NULL，则 SUBSTRING 返回 NULL。

流数据

与大多数 SQL 字符串函数不同，SUBSTRING 可以与流数据一起使用。字符串表达式可以是数据类型为 %Stream.GlobalCharacter 或 %Stream.GlobalBinary 的字段。 SUBSTRING 将提取的流数据子集作为 %String 数据类型返回。如果 start=1 并且省略了 length，则 SUBSTRING 以 %String 形式返回完整的流数据值。

因此，SUBSTRING 可用于将字符流数据作为字符串提供给其他 SQL 字符串函数。以下示例使用 SUBSTRING 允许 CHARINDEX 在包含 DNA 核苷酸序列的 %Stream.GlobalCharacter 字段的前 1000 个字符中搜索子字符串 TTAGGG 的第一次出现，并将该位置作为整数返回：

SELECT CHARINDEX('TTAGGG',SUBSTRING(DNASeq,1,1000)) FROM Sample.DNASequences

SUBSTRING or SUBSTR

• SUBSTRING 从从字符串表达式的开头算起的起始位置提取子字符串。 SUBSTR 可以从字符串的开头或结尾提取子字符串。
• SUBSTRING 可以与流数据一起使用； SUBSTR 不能与流数据一起使用。

示例

此示例返回字符串“forward”：

SELECT {fn SUBSTRING( 'forward pass',1,7 )} AS SubText

forward

此示例返回字符串“pass”：

SELECT {fn SUBSTRING( 'forward pass',9,4 )} AS SubText

pass

以下示例返回每个名称的前四个字符：

SELECT Name,SUBSTRING(Name,1,4) AS FirstFour
FROM Sample.Person

以下示例演示了 SUBSTRING 的另一种语法形式。此示例在功能上与上一个示例相同：

SELECT Name,SUBSTRING(Name FROM 1 FOR 4) AS FirstFour
FROM Sample.Person

以下示例显示长度如何减少小于 1 的起始值。（起始值 0 减少长度 1，起始值 -1 减少长度 2，依此类推。）在这种情况下，长度减 3，因此只返回一个字符（“A”）：

SELECT {fn SUBSTRING( 'ABCDEFG',-2,4 )} AS SubText

A

第135章 SQL函数 SUBSTR

返回从指定字符串表达式派生的子字符串的字符串函数。

大纲

SUBSTR(string-expression,start[,length])

参数

• string-expression - 要从中派生子字符串的字符串表达式。表达式可以是列名、字符串文字或另一个标量函数的结果，其中基础数据类型可以表示为任何字符类型（例如 CHAR 或 VARCHAR）。
• start - 一个整数，它指定子字符串在字符串表达式中的开始位置。正的起始位置指定从字符串开头开始的字符数。 string-expression1 中的第一个字符位于位置 1。负起始位置指定从字符串末尾算起的字符数。如果 start 为 0（零），则将其视为 1。
• length - 可选 — 一个正整数，指定要返回的子字符串的长度。此值指定子字符串在起始位置右侧结束长度字符。如果省略，则子字符串从字符串表达式的开头到结尾。如果长度为 0 或负数， 返回 NULL。

描述

因为 start 可以是负数，所以可以从原始字符串的开头或结尾获取子字符串。

作为参数传递给 SUBSTR 的浮点数通过截断小数部分转换为整数。

• 如果 start 为 0、–0 或 1，则返回的子字符串从字符串的第一个字符开始。
• 如果 start 是负数，则返回的子字符串从字符串末尾开始该数量的字符，-1 表示字符串的最后一个字符。如果负数太大以至于从字符串末尾向后计数的值将位于字符串开头之前，则返回的子字符串从字符串的第一个字符开始。
• 如果 start 超过了字符串的结尾，则返回 NULL。
• 如果长度大于字符串中剩余的字符，则返回从字符串开头到结尾的子字符串。
• 如果长度小于 1，则返回 NULL。
• 如果 start 或 length 为 NULL，则返回 NULL。

SUBSTR 不能与流数据一起使用。如果 string-expression 是流字段，则 SUBSTR 生成 SQLCODE -37。使用 SUBSTRING 从流数据中提取子字符串。

Oracle 兼容性支持 SUBSTR。

示例

以下示例返回子字符串 CDEFG，因为它指定子字符串从第三个字符 (C) 开始并继续到字符串的末尾：

SELECT SUBSTR('ABCDEFG',3) AS Sub

CDEFG

以下示例返回子字符串 CDEF，因为它指定子字符串从第三个字符 (C) 开始并持续四个字符（直到 F）：

SELECT SUBSTR('ABCDEFG',3,4) AS Sub

CDEF

以下示例返回子字符串 CDEF，因为它指定 应首先从原始字符串的末尾倒数五个字符，然后返回接下来的四个字符：

SELECT SUBSTR('ABCDEFG',-5,4) AS Sub

CDEF

第134章 SQL函数 STUFF

字符串函数，用于替换字符串中的子字符串。

大纲

STUFF(string,start,length,substring)

参数

• string - 作为子字符串替换目标的字符串表达式。
• start - 替换的起点，指定为正整数。从字符串开头开始的字符数，从 1 开始计数。允许的值为 0 到字符串的长度。要追加字符，请指定 0 的开头和 0 的长度。空字符串或非数字值被视为 0。
• length - 要替换的字符数，指定为正整数。要插入字符，请将长度指定为 0。要在开始后替换所有字符，请指定大于现有字符数的长度。空字符串或非数字值被视为 0。
• substring - 一个字符串表达式，用于替换由其起始点和长度标识的子字符串。可以比它替换的子字符串更长或更短。可以是空字符串。

描述

STUFF 用另一个子字符串替换一个子字符串。它标识要替换为位置和长度的子字符串，并将其替换为子字符串。

此函数提供与 Transact-SQL 实现的兼容性。

替换子字符串可能比原始值长或短。要删除原始值，子字符串可以是空字符串 ('')。

起始值必须在字符串的当前长度范围内。您可以通过指定起始值 0 将子字符串附加到字符串的开头。空字符串或非数字值被视为 0。

为 start、length 或 substring 参数指定 NULL 将返回 NULL。

STUFF 不能将 %Stream.GlobalCharacter 字段用于字符串或子字符串参数。尝试这样做会生成 SQLCODE -37 错误。

REPLACE 和 STUFF

REPLACE 和 STUFF 都执行子字符串替换。 REPLACE 按数据值搜索子字符串。 STUFF 按字符串位置和长度搜索子字符串。

示例

以下示例显示了单字符替换，将 BOLT 转换为 BELT：

SELECT STUFF('BOLT',2,1,'E')

BELT

以下示例将一个 8 个字符的子字符串 (Kentucky) 替换为一个较长的 12 个字符的子字符串和一个较短的 2 个字符的子字符串：

SELECT STUFF('In my old Kentucky home',11,8,'Rhode Island'),
       STUFF('In my old Kentucky home',11,8,'KY')
       
In my old Rhode Island home	In my old KY home

以下示例插入一个子字符串：

SELECT STUFF('In my old Kentucky home',19,0,' (KY)')

In my old Kentucky (KY) home

以下示例将一个子字符串附加到字符串的开头：

SELECT STUFF('In my old Kentucky home',0,0,'The sun shines bright ')

The sun shines bright In my old Kentucky home

以下示例通过将 8 个字符的子字符串替换为空字符串来删除它：

SELECT STUFF('In my old Kentucky home',11,8,'')

In my old  home

第133章 SQL函数 STRING

将表达式转换并连接成字符串的函数。

大纲

STRING(string1[,string2][,...][,stringN])

参数

• string - 表达式，可以是字段名称、字符串文字、数字或另一个函数的结果，其中基础数据类型可以表示为任何字符类型（例如 CHAR 或 VARCHAR）。如果指定了字段名称，则使用逻辑值。

描述

STRING 将一个或多个字符串转换为 STRING 格式，然后将这些字符串连接成一个字符串。不执行大小写转换。

STRING 在字符串转换之前将数字转换为其规范形式。因此，它执行算术运算，从数字中删除前导和尾随零以及前导加号。

如果字符串参数之一为 NULL，则 STRING 返回 NULL。如果字符串参数之一是空字符串 ('')，则 STRING 将连接其他参数。 STRING 保留空格。

可以使用 %SQLSTRING 函数转换数据值以进行区分大小写的字符串比较，或使用 %SQLUPPER 函数转换数据值以进行不区分大小写的字符串比较。

示例

在以下嵌入式 SQL 示例中，STRING 将三个子字符串连接成一个字符串。该示例显示了空格、空字符串和 NULL 的处理：

/// d ##class(PHA.TEST.SQLFunction).String()
ClassMethod String()
{
	&sql(
		SELECT STRING('a','b','c'),
			STRING('a',' ','c'),
			STRING('a','','c'),
			STRING('a',NULL,'c')
		INTO :w,:x,:y,:z
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Resulting string is:",w
		w !,"Resulting string is:",x
		w !,"Resulting string is:",y
		w !,"Resulting string is:",z 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).String()
 
Resulting string is:abc
Resulting string is:a c
Resulting string is:ac
Resulting string is:

在以下嵌入式 SQL 示例中，STRING 将数字转换为字符串。所有这些 STRING 函数都返回字符串“123”：

/// d ##class(PHA.TEST.SQLFunction).String1()
ClassMethod String1()
{
	&sql(
		SELECT STRING(123),
			STRING(+00123.00),
			STRING('1',23),
			STRING(1,(10*2)+3)
		INTO :w,:x,:y,:z
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Resulting string is:",w
		w !,"Resulting string is:",x
		w !,"Resulting string is:",y
		w !,"Resulting string is:",z 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).String1()
 
Resulting string is:123
Resulting string is:123
Resulting string is:123
Resulting string is:123

在以下嵌入式 SQL 示例中，STRING 从字段中检索示例数据并将其连接成一个字符串：

/// d ##class(PHA.TEST.SQLFunction).String2()
ClassMethod String2()
{
	&sql(
		SELECT STRING(Name,Age)
			INTO :x
		FROM Sample.Person
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Resulting string is:",x 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).String2()
 
Resulting string is:yaoxin31

第132章 SQL函数 STR

将数字转换为字符串的函数。

大纲

STR(number[,length[,decimals]])

参数

• number - 解析为数字的表达式。它可以是字段名称、数字或其他函数的结果。如果指定了字段名称，则使用逻辑值。
• length - 可选 — 一个整数，指定所需输出字符串的总长度，包括所有字符（数字、小数点、符号、空格）。默认值为 10。
• decimals - 可选 — 一个整数，指定要包含的小数点右侧的位数。默认值为 0。

描述

STR 将数字转换为 STRING 格式，根据长度和小数的值截断数字。长度参数必须足够大以包含数字的整个整数部分，并且，如果指定小数，则该小数位数加 1（用于小数点）。如果长度不够大，则 STR 将返回一个星号 (*) 字符串，该字符串等于长度。

STR 在字符串转换之前将数字转换为其规范形式。因此，它执行算术运算，从数字中删除前导和尾随零以及前导加号。

如果 number 参数为 NULL，则 STR 返回 NULL。如果 number 参数是空字符串 ('')，则 STR 返回空字符串。 STRING 保留空格。

示例

在以下嵌入式 SQL 示例中，STR 将数字转换为字符串：

/// d ##class(PHA.TEST.SQLFunction).Str()
ClassMethod Str()
{
	&sql(
		SELECT STR(123),
			STR(123,4),
			STR(+00123.45,3),
			STR(+00123.45,3,1),
			STR(+00123.45,5,1)
		INTO :v,:w,:x,:y,:z
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Resulting STR:",v," string"
		w !,"Resulting STR:",w," string"
		w !,"Resulting STR:",x," string"
		w !,"Resulting STR:",y," string"
		w !,"Resulting STR:",z," string" 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Str()
 
Resulting STR:       123 string
Resulting STR: 123 string
Resulting STR:123 string
Resulting STR:*** string
Resulting STR:123.4 string

第一个 STR 函数返回一个由 7 个前导空格和数字 123 组成的字符串；七个前导空格是因为默认字符串长度为 10。第二个 STR 函数返回字符串“123”；注意返回长度为 4 的字符串所需的前导空格。第三个 STR 函数返回字符串“123”；数字被放入规范形式，小数默认为 0。第四个 STR 函数返回“***”，因为字符串长度不足以包含指定的整个数字；星号的数量表示字符串长度。第五个STR函数返回“123.4”；请注意，长度必须为 5 才能包含十进制数字。

第131章 SQL函数 SQUARE

返回数字平方的标量数值函数。

大纲

SQUARE(numeric-expression)

参数

• numeric-expression - 解析为数值的表达式。

SQUARE 返回 NUMERIC 或 DOUBLE 数据类型。如果 numeric-expression 是数据类型 DOUBLE，则 SQUARE 返回 DOUBLE；否则，它返回 NUMERIC。

描述

SQUARE 返回数值表达式的平方。如果传递 NULL 值，SQUARE 返回 NULL。

SQUARE 返回的精度和小数位数与 SQL 乘法运算符返回的相同。

示例

以下嵌入式 SQL 示例返回整数 0 到 10 的平方：

/// d ##class(PHA.TEST.SQLFunction).Square()
ClassMethod Square()
{
	s a = 0
	while a < 11 {
		&sql(
			SELECT SQUARE(:a) INTO :b
		)
		if SQLCODE '= 0 {
			w !,"Error code ",SQLCODE 
		} else {
			w !,"The square of ",a," = ",b
			s a = a + 1 
		}
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Square()
 
The square of 0 = 0
The square of 1 = 1
The square of 2 = 4
The square of 3 = 9
The square of 4 = 16
The square of 5 = 25
The square of 6 = 36
The square of 7 = 49
The square of 8 = 64
The square of 9 = 81
The square of 10 = 100

第130章 SQL函数 SQRT

返回给定数值表达式的平方根的数值函数。

大纲

SQRT(numeric-expression)

{fn SQRT(numeric-expression)}

参数

• numeric-expression - 解析为计算平方根的正数的表达式。

SQRT 返回 NUMERIC 或 DOUBLE 数据类型。如果 numeric-expression 是数据类型 DOUBLE，则 SQRT 返回 DOUBLE；否则，它返回 NUMERIC。

描述

SQRT 返回 numeric-expression 的平方根。 numeric-expression 必须是正数。负数值表达式（-0 除外）会生成 SQLCODE -400 错误。如果传递 NULL 值，SQRT 返回 NULL。

SQRT 返回一个精度为 36、小数位数为 18 的值。

SQRT 可以指定为常规标量函数或 ODBC 标量函数（使用大括号语法）。

示例

以下示例显示了两种 SQRT 语法形式。两者都返回 49 的平方根：

SELECT SQRT(49) AS SRoot,{fn SQRT(49)} AS ODBCSRoot

7	7

以下嵌入式 SQL 示例返回整数 0 到 10 的平方根：

/// d ##class(PHA.TEST.SQLFunction).Sort()
ClassMethod Sort()
{
	s a = 0
	while a < 11 {
		&sql(
			SELECT SQRT(:a) INTO :b
		)
		if SQLCODE '= 0 {
			w !,"Error code ",SQLCODE 
		} else {
			w !,"The square root of ",a," = ",b
			s a = a + 1 
		}
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Sort()
 
The square root of 0 = 0
The square root of 1 = 1
The square root of 2 = 1.414213562373095049
The square root of 3 = 1.732050807568877294
The square root of 4 = 2
The square root of 5 = 2.236067977499789697
The square root of 6 = 2.449489742783178098
The square root of 7 = 2.645751311064590591
The square root of 8 = 2.828427124746190098
The square root of 9 = 3
The square root of 10 = 3.162277660168379332

第129章 SQL函数 %SQLUPPER

将值排序为大写字符串的排序规则函数。

大纲

%SQLUPPER(expression[,maxlen]) 

%SQLUPPER expression

参数

• expression - 字符串表达式，可以是列名、字符串文字或另一个函数的结果，其中基础数据类型可以表示为任何字符类型（例如 CHAR 或 VARCHAR）。表达式可以是子查询。
• maxlen - 可选 — 一个整数，指定整理后的值将被截断为 maxlen 的值。请注意， maxlen 包括附加的前导空格。您可以用双括号将 maxlen 括起来以抑制文字替换：((maxlen))。

描述

SQLUPPER 是默认排序规则。

%SQLUPPER 将表达式转换为排序为（不区分大小写）大写字符串的格式。 %SQLUPPER 将所有字母字符转换为大写，从字符串中去除尾随空格（空格、制表符等），然后在字符串开头添加一个前导空格。这个附加的空格会导致 NULL 和数值被整理为字符串。

SQL 在将数字传递给函数之前将数值转换为规范形式（删除前导零和尾随零、扩展指数等）。 SQL 不会将数字字符串转换为规范形式。

因为 %SQLUPPER 将空格附加到所有值，所以它将 NULL 值作为空格进行整理，字符串长度为 1。 %SQLUPPER 将任何仅包含空格（空格、制表符等）的值整理为 SQL 空字符串 ('')。当 %SQLUPPER 将空格附加到空（零长度）字符串时，它会将空格加上空字符串的内部表示形式 $CHAR(0) 进行整理，从而导致字符串长度为 2。

可选的 maxlen 参数在索引或整理时将转换后的表达式字符串截断为指定的字符数。例如，如果插入带有 maxlen 截断的字符串，则插入完整的字符串，并且可以通过 SELECT 语句检索；此字符串的全局索引被截断为指定长度。这意味着 ORDER BY 和比较操作仅计算截断的索引字符串。这种截断对于对超过下标的最大字符长度的字符串进行索引特别有用。使用 maxlen 参数，如果需要对长字段进行索引，可以使用截断长度参数。

%SQLUPPER 转换表达式后执行 maxlen 截断；如果 maxlen 超过转换后的表达式的长度，则不添加填充。最大字符串长度为 3,641,144 个字符； maxlen 没有强制执行最大值。

可以使用 %SYSTEM.Util 类的 Collation() 方法在 ObjectScript 中执行相同的排序规则转换：

DHC-APP>  WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",7)
 THE QUICK, BROWN FOX.

也可以使用 SQLUPPER() 方法调用从 ObjectScript 调用此函数：

DHC-APP>  WRITE $SYSTEM.SQL.SQLUPPER("The quick, BROWN fox.")
 THE QUICK, BROWN FOX.

这两种方法都支持 SQLUPPER 转换后的截断。请注意，截断长度必须包括附加的空白：

DHC-APP>WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",7,6)
 THE Q
DHC-APP>WRITE $SYSTEM.SQL.SQLUPPER("The quick, BROWN fox.",6)
 THE Q

注意：要将系统范围的默认排序规则从 %SQLUPPER（不区分大小写）更改为 %SQLSTRING（区分大小写），请使用以下命令：

  WRITE $$SetEnvironment^%apiOBJ("collation","%Library.String","SQLSTRING")

发出此命令后，必须清除索引，重新编译所有类，然后重建索引。不要在其他用户访问表的数据时重建索引。这样做可能会导致查询结果不准确。

其他大小写转换函数

%SQLUPPER 函数是 SQL 中转换数据值以进行不区分大小写的比较或排序规则的首选方法。 %SQLUPPER 在数据的开头添加一个前导空格，这会强制将数字数据和 NULL 值解释为字符串。

以下是转换数据值大小写的其他函数：

• UPPER 和 UCASE：将字母转换为大写，对数字字符、标点字符、嵌入空格以及前导和尾随空格没有影响。不强制将数字解释为字符串。
• LOWER 和 LCASE：将字母转换为小写，对数字字符、标点字符、嵌入空格以及前导和尾随空格没有影响。不强制将数字解释为字符串。
• %SQLSTRING：不转换字母大小写。但是，它会在数据的开头添加一个前导空格，这会强制将数字数据和 NULL 值解释为字符串。

字母数字整理顺序

大小写转换函数使用不同的算法整理以数字开头的数据值，如下所示：

• %MVR - 6 Oak Avenue, 66 Main Street, 66 Oak Street, 641 First Place, 665 Ash Drive, 672 Main Court, 709 Oak Avenue, 5988 Clinton Avenue, 6023 Washington Court, 6090 Elm Court, 6185 Clinton Drive, 6209 Clinton Street, 6284 Oak Drive, 6310 Franklin Street, 6406 Maple Place, 6572 First Avenue, 6643 First Street, 6754 Oak Court, 6986 Madison Blvd, 7000 Ash Court,
• %SQLUPPER、%SQLSTRING 和所有其他大小写转换函数 - 5988 Clinton Avenue, 6 Oak Avenue, 6023 Washington Court, 6090 Elm Court, 6185 Clinton Drive, 6209 Clinton Street, 6284 Oak Drive, 6310 Franklin Street, 6406 Maple Place, 641 First Place, 6572 First Avenue, 66 Main Street, 66 Oak Street, 6643 First Street, 665 Ash Drive, 672 Main Court, 6754 Oak Court, 6986 Madison Blvd, 7000 Ash Court, 709 Oak Avenue,

示例

以下查询使用带有字符串截断的 %SQLUPPER 以大写形式返回每个名称的前两个字符。请注意，字符串截断是 3（不是 2），因为 %SQLUPPER 添加了前导空格。 ORDER BY 子句使用这个两个字符的字段将行放在一个粗略的排序顺序中：

SELECT Name, %SQLUPPER(Name,3) AS FirstTwo
FROM Sample.Person
ORDER BY FirstTwo

以下示例将 %SQLUPPER 应用于子查询：

SELECT TOP 5 Name, %SQLUPPER((SELECT Name FROM Sample.Company),10) AS Company
FROM Sample.Person

第128章 SQL函数 %SQLSTRING

将值排序为字符串的排序规则函数。

大纲

%SQLSTRING(expression[,maxlen])

%SQLSTRING expression

参数

• expression - 字符串表达式，可以是列名、字符串文字或另一个函数的结果，其中基础数据类型可以表示为任何字符类型（例如 CHAR 或 VARCHAR）。表达式可以是子查询。
• maxlen - 可选 — 一个正整数，指定整理后的值将被截断为 maxlen 的值。请注意， maxlen 包括附加的前导空格。可以用双括号将 maxlen 括起来以抑制文字替换：((maxlen))。

描述

%SQLSTRING 将表达式转换为按（区分大小写）字符串排序的格式。 %SQLSTRING 从字符串中去除尾随空格（空格、制表符等），然后在字符串的开头添加一个前导空格。这个附加的空格强制将 NULL 和数值作为字符串进行整理。从数字中删除前导零和尾随零。

因为 %SQLSTRING 将空格附加到所有值，所以它将 NULL 值整理为空格，字符串长度为 1。 %SQLSTRING 将任何仅包含空格（空格、制表符等）的值整理为 SQL 空字符串('')。当 %SQLSTRING 将空格附加到空（零长度）字符串时，它会将空格加上空字符串的内部表示形式 $CHAR(0) 进行整理，从而导致字符串长度为 2。

可选的 maxlen 参数在索引或整理时将表达式字符串截断为指定的字符数。例如，如果插入带有 maxlen 截断的字符串，则插入完整的字符串，并且可以通过 SELECT 语句检索；此字符串的全局索引被截断为指定长度。这意味着 ORDER BY 和比较操作仅评估截断的索引字符串。这种截断对于对超过下标的最大字符长度的字符串进行索引特别有用。使用 maxlen 参数，如果需要对长字段进行索引，可以使用截断长度参数。

%SQLSTRING 在转换表达式后执行 maxlen 截断；如果 maxlen 超过转换后的表达式的长度，则不添加填充。最大字符串长度为 3,641,144 个字符； maxlen 没有强制执行最大值。

可以使用 %SYSTEM.Util 类的 Collation() 方法在 ObjectScript 中执行相同的排序规则转换：

DHC-APP>  WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",8)
 The quick, BROWN fox.

也可以使用 SQLSTRING() 方法调用从 ObjectScript 调用此函数：

DHC-APP>  WRITE $SYSTEM.SQL.SQLSTRING("The quick, BROWN fox.")
 The quick, BROWN fox.

这两种方法都支持 SQLSTRING 转换后的截断。请注意，截断长度必须包括附加的空白：

DHC-APP>WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",8,6)
 The q
DHC-APP>WRITE $SYSTEM.SQL.SQLSTRING("The quick, BROWN fox.",6)
 The q

注意：要将系统范围的默认排序规则从 %SQLUPPER（不区分大小写）更改为 %SQLSTRING（区分大小写），请使用以下命令：

  WRITE $$SetEnvironment^%apiOBJ("collation","%Library.String","SQLSTRING")

发出此命令后，必须清除索引，重新编译所有类，然后重建索引。不要在其他用户访问表的数据时重建索引。这样做可能会导致查询结果不准确。

示例

以下查询在 WHERE 子句中使用 %SQLSTRING 来执行区分大小写的选择：

SELECT Name FROM Sample.Person
WHERE %SQLSTRING Name %STARTSWITH %SQLSTRING 'Al'
ORDER BY Name

默认情况下，%STARTSWITH 字符串比较不区分大小写。此示例使用 %SQLSTRING 格式使此比较区分大小写。它返回所有以“Al”开头的名称（例如 Allen、Alton 等）。请注意，使用 %STARTSWITH 时，应将 %SQLSTRING排序规则应用于语句的两侧。

以下示例使用带有字符串截断的 %SQLSTRING 来返回每个名称的前两个字符。请注意，字符串截断是 3（不是 2），因为 %SQLSTRING 添加了前导空格。 ORDER BY 子句使用这个两个字符的字段将行放在一个粗略的排序顺序中：

SELECT Name, %SQLSTRING(Name,3) AS FirstTwo
FROM Sample.Person
ORDER BY FirstTwo

此示例在不更改字母大小写的情况下返回截断的值。

以下示例将 %SQLSTRING 应用于子查询：

SELECT TOP 5 Name, %SQLSTRING((SELECT Name FROM Sample.Company),10) AS Company
FROM Sample.Person

第127章 SQL函数 SPACE

返回一串空格的字符串函数。

大纲

SPACE(count)

{fn SPACE(count)}

参数

• count - 整数表达式，指定要返回的空格数。

描述

SPACE 返回一个空格字符串 count 个空格长。如果 count 是数字字符串、十进制数或混合数字字符串， 会将其解析为其整数部分。如果 count 是负数或非数字字符串将其解析为 0。

要从字符串中删除空格，请使用 LTRIM（前导空格）或 RTRIM（尾随空格）。

注意：不应将 SPACE 函数与 SPACE 归类类型混淆。 SPACE 排序规则在一个值前面添加一个空格，强制将其作为字符串进行评估。为了建立 SPACE 归类，CREATE TABLE 提供了一个 %SPACE 归类关键字，ObjectScript 提供了 %SYSTEM.Util 类的 Collation() 方法。

示例

以下嵌入式 SQL 示例返回名称字段长度的空格字符串：

/// d ##class(PHA.TEST.SQLFunction).Space()
ClassMethod Space()
{
	&sql(
		SELECT SPACE(LENGTH(name))
			INTO :a
		FROM Sample.Person
	)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE 
	} else {
		w !,"Leave this much space:",a,"for names" 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLFunction).Space()
 
Leave this much space:              for names