Caché SQL Reference
A timestamp function that returns the current UTC date and time.
||Optional Specifies the time precision as the number of digits of fractional seconds. Valid values are the integers 0 through 9. The default is 0 (no fractional seconds); this default is configurable.
can be issued with no arguments, or with the optional precision argument. It returns Universal Time Constant (UTC) time in %TimeStamp format. Its ODBC data type is TIMESTAMP
, LENGTH is 16, and PRECISION is 19.
Because UTC time does not depend on the local timezone and is not subject to local time variants (such as Daylight Saving Time
), this function is useful for applying consistent timestamps when users in different time zones access the same database.
The datetime string returned is of the format:
Where f represents fractional seconds of precision. GETUTCDATE
values are displayed in this format in Display mode, ODBC mode, or Logical mode.
You can also use the Caché ObjectScript $ZTIMESTAMP
special variable to return a UTC timestamp.
To change the default datetime string format, use the SET OPTION
command with the various date and time options.
Typical uses for GETUTCDATE
are in the SELECT
statement select list or in the WHERE
clause of a query. In designing a report, GETUTCDATE
can be used to print the current date and time each time the report is produced. GETUTCDATE
is also useful for tracking activity, such as logging the time that a transaction occurred.
returns the current UTC date and time as a TIMESTAMP.
return the current local date. CURTIME
return the current local time. These functions use the DATE or TIME data type. None of these functions support precision.
A TIMESTAMP data type stores and displays its value in the same format. The TIME and DATE data types store their values as integers in $HOROLOG
format and can be displayed in a variety of formats.
Note that all Caché SQL time and date functions except GETUTCDATE
are specific to the local time zone setting.
Fractional Seconds Precision
can return up to nine digits of precision. The number of digits of precision returned is set using the precision
argument. The default for the precision
argument can be configured using the following:
Specify an integer 0 through 9 (inclusive) for the default number of decimal digits of precision to return. The default is 0. The actual precision returned is platform dependent; precision
digits in excess of the precision available on your system are returned as zeroes.
Fractional seconds are always truncated, not rounded, to the specified precision.
The following example returns the current date and time as a UTC timestamp and as a local timestamp:
SELECT GETUTCDATE() AS UTCDateTime,
GETDATE() AS LocalDateTime
The following example returns the current UTC date and time with fractional seconds having two digits of precision:
SELECT GETUTCDATE(2) AS DateTime
The following example sets the LastUpdate field in the selected row of the Orders table to the current UTC system date and time.
UPDATE Orders SET LastUpdate = GETUTCDATE()
In the following example, the CREATE TABLE
statement uses GETUTCDATE
to set a default value for the OrderRcvd field:
CREATE TABLE Orders(
OrderId INT NOT NULL,
ItemName CHAR(40) NOT NULL,
Quantity INT NOT NULL,
OrderRcvd TIMESTAMP DEFAULT GETUTCDATE())