GETDATE

GETDATE([precision])

precision

An optional argument that specifies the time precision, specified as the number of digits of fractional seconds. The default is 0 (no fractional seconds).

GETDATE can be issued with no arguments, or with the optional precision argument. It returns the time in %TimeStamp format. Its ODBC type is TIMESTAMP, LENGTH is 16, and PRECISION is 19.

The datetime string returned is of the format:

yyyy-mm-dd hh:mm:ss.ffff

Where “f” represents the optional fractional seconds of precision. You can return any number of digits of precision. The actual precision is platform dependent; digits in excess of that precision are returned as zeroes.

To change the default datetime string format, use the SET OPTION command with the various date and time options.

Typical uses for GETDATE are in the 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 for 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. The functions use the DATE or TIME data type. None of these functions support precision.

These data types perform differently when using embedded SQL. 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; when displayed in SQL they are converted to date or time display format; when returned from embedded SQL they are returned as integers. You can use the CAST or CONVERT function to change the datatype of dates and times.

All Caché SQL timestamp functions are specific to the local time zone setting. To get a current timestamp that is universal (independent of time zone) use the Caché ObjectScript $ZTIMESTAMP special variable.

The following example returns the current date and time:

SELECT DISTINCT GETDATE() AS DateTime
FROM Sample.Person

The following example returns the current date and time with two digits of precision:

SELECT DISTINCT GETDATE(2) AS DateTime
FROM Sample.Person

The following embedded SQL example compares local (time zone specific) and universal (time zone independent) time stamps:

  SET b=$ZDATETIME($ZTIMESTAMP,3)
  &sql(SELECT DISTINCT GETDATE()
  INTO :a
  FROM Sample.Person)
  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())