Special Variables (SQL)
System-supplied variables.
Synopsis
$HOROLOG $JOB $NAMESPACE $TLEVEL $USERNAME $ZHOROLOG $ZJOB $ZPI $ZTIMESTAMP $ZTIMEZONE $ZVERSION
Description
InterSystems SQL directly supports a number of the ObjectScript special variables. These variables contain system-supplied values. They can be used wherever a literal value can be specified in InterSystems SQL.
SQL special variable names are not case-sensitive. Most can be specified using an abbreviation.
| Variable Name | Abbreviation | Data Type Returned | Use |
|---|---|---|---|
| $HOROLOG | $H | %String/VARCHAR | Local date and time for the current process |
| $JOB | $J | %String/VARCHAR | Job ID of the current process |
| $NAMESPACE | none | %String/VARCHAR | Current namespace name |
| $TLEVEL | $TL | %Integer/INTEGER | Current transaction nesting level |
| $USERNAME | none | %String/VARCHAR | User name for the current process |
| $ZHOROLOG | $ZH | %Numeric/NUMERIC(21,6) | Number of elapsed seconds since InterSystems IRIS startup |
| $ZJOB | $ZJ | %Integer/INTEGER | Job status for the current process |
| $ZPI | none | %Numeric/NUMERIC(21,18) | The numeric constant PI |
| $ZTIMESTAMP | $ZTS | %String/VARCHAR | Current date and time in Coordinated Universal Time format |
| $ZTIMEZONE | $ZTZ | %Integer/INTEGER | Local time zone offset from GMT |
| $ZVERSION | $ZV | %String/VARCHAR | The current version of InterSystems IRIS |
For further details, refer to the corresponding ObjectScript special variable, as described in the ObjectScript Reference.
Examples
The following example returns a result set that includes the current date and time:
SELECT TOP 5 Name,$H FROM Sample.Person
The following example only returns a result set if the time zone is within the continental United States:
SELECT TOP 5 Name,Home_State FROM Sample.Person WHERE $ZTIMEZONE BETWEEN 300 AND 480
