Caché SQL Reference
GETDATE
|
|
A date/time function that returns the current local date and time.
Synopsis
GETDATE returns a
timestamp in %TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff).
To change the default datetime string format, use the
SET OPTION command with the various date and time options.
GETDATE can be used in a
SELECT statement select list or in the
WHERE clause of a query. In designing a report,
GETDATE can be used to print the current date and time each time the report is produced.
GETDATE is also useful for tracking activity, such as logging the time that a transaction occurred.
To return just the current date, use
CURDATE or
CURRENT_DATE. To return just the current time, use
CURRENT_TIME or
CURTIME. 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. They can be displayed in either Display format or Logical (storage) format. You can use the
CAST or
CONVERT function to change the data type of dates and times.
GETDATE returns the current local date and time. All Caché SQL timestamp, date, and time functions except
GETUTCDATE are specific to the local time zone setting.
GETUTCDATE returns the current UTC (universal) date and time as a TIMESTAMP value. You can also use the ObjectScript
$ZTIMESTAMP special variable to get a current timestamp that is universal (independent of time zone).
Fractional Seconds Precision
GETDATE 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 in TIMESTAMP format:
SELECT GETDATE() AS DateTime
The following example returns the current date and time with two digits of precision:
SELECT GETDATE(2) AS DateTime
The following Embedded SQL example compares local (time zone specific) and universal (time zone independent) timestamps:
&sql(SELECT GETDATE(),GETUTCDATE() INTO :a,:b)
IF SQLCODE'=0 {
WRITE !,"Error code ",SQLCODE }
ELSE {
WRITE !,"Local timestamp is: ",a
WRITE !,"UTC timestamp is: ",b
WRITE !,"$ZTIMESTAMP is: ",$ZDATETIME($ZTIMESTAMP,3,,3)
}
The following example sets the LastUpdate field in the selected row of the Orders table to the current system date and time.
GETDATE returns the current date and time as an ODBC timestamp:
UPDATE Orders SET LastUpdate = GETDATE()
WHERE Orders.OrderNumber=:ord
In the following example, the
CREATE TABLE statement uses
GETDATE to set a default value for the StartDate field:
CREATE TABLE Employees(
EmpId INT NOT NULL,
LastName CHAR(40) NOT NULL,
FirstName CHAR(20) NOT NULL,
StartDate TIMESTAMP DEFAULT GETDATE())