Caché SQL Reference
GETDATE
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A timestamp function that returns the current local date and time.
Synopsis
GETDATE([precision])
Arguments
precision 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. A precision value is optional, the parentheses are mandatory.
Description
GETDATE can be issued with no arguments, or with the optional precision argument. It returns the current local date and time in %TimeStamp format. Its ODBC type is TIMESTAMP, LENGTH is 16, and PRECISION is 19. GETDATE returns the current local date and time for this timezone; it adjusts for local time variants, such as Daylight Saving Time.
The datetime string returned is of the format:
yyyy-mm-dd hh:mm:ss.ffff
Where “f” represents fractional seconds of precision. GETDATE values are displayed in this format in Display mode, ODBC mode, or Logical mode.
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.
GETDATE can be used in CREATE TABLE to specify a field’s default value. GETDATE is a synonym for CURRENT_TIMESTAMP and is provided for compatibility with Sybase and Microsoft SQL Server.
The CURRENT_TIMESTAMP and NOW functions can also be used to return the current date and time as data type TIMESTAMP. CURRENT_TIMESTAMP supports precision, NOW does not support precision.
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.
Universal Time (UTC)
GETDATE returns the current local date and time as a TIMESTAMP. 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. You can also use the Caché 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.
Examples
The following example returns the current date and time:
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) time stamps:
  SET b=$ZDATETIME($ZTIMESTAMP,3)
  &sql(SELECT GETDATE()
  INTO :a)
  IF SQLCODE'=0 {
    WRITE !,"Error code ",SQLCODE }
  ELSE {
    WRITE !,"GetDate is:    ",a
    WRITE !,"ZTimestamp is: ",b }
 
The following example:
UPDATE Orders SET LastUpdate = GETDATE()
  WHERE Orders.OrderNumber=:ord
sets the LastUpdate field in the selected row of the Orders table to the current system date and time.
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())
See Also