GETDATE (SQL)
Synopsis
GETDATE([precision])
Arguments
Argument | Description |
---|---|
precision | Optional — A positive integer that specifies the time precision as the number of digits of fractional seconds. The default is 0 (no fractional seconds); this default is configurable. A precision value is optional, the parentheses are mandatory. |
Description
GETDATE returns the current local date and time for this timezone as a timestamp; it adjusts for local time variants, such as Daylight Saving Time.
GETDATE can return a timestamp in either %TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff) or %PosixTime data type format (an encoded 64-bit signed integer). The following rules determine which timestamp format is returned:
-
If the current timestamp is being supplied to a field of data type %PosixTime, the current timestamp value is returned in POSIXTIME data type format. For example, WHERE PosixField=GETDATE() or INSERT INTO MyTable (PosixField) VALUES (GETDATE()).
-
If the current timestamp is being supplied to a field of data type %TimeStamp, the current timestamp value is returned in TIMESTAMP data type format. Its ODBC type is TIMESTAMP, LENGTH is 16, and PRECISION is 19. For example, WHERE TSField=GETDATE() or INSERT INTO MyTable (TSField) VALUES (GETDATE()).
-
If the current timestamp is being supplied without context, the current timestamp value is returned in TIMESTAMP data type format. For example, SELECT GETDATE().
To change the default datetime string format, use the SET OPTION command with the various date and time options.
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 local date and time as a timestamp in either TIMESTAMP or POSIXTIME formats. 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. 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)
All InterSystems 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 either a TIMESTAMP value or a POSIXTIME 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:
-
SET OPTION with the TIME_PRECISION option.
-
The system-wide $SYSTEM.SQL.Util.SetOption()Opens in a new tab method configuration option DefaultTimePrecision. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab which displays Default time precision; the default is 0.
-
Go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, SQL. View and edit the current setting of Default time precision for GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP.
Specify an integer 0 through 9 (inclusive) for the precision argument. 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.
Examples
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 a SELECT statement select list or in the WHERE clause of a query. 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 example compares local (time zone specific) and universal (time zone independent) timestamps:
SELECT GETDATE(), GETUTCDATE()
The following example sets the LastUpdate field in the selected row of the Orders table to the current system date and time. If LastUpdate is data type %TimeStamp, GETDATE returns the current date and time as an ODBC timestamp; if LastUpdate is data type %PosixTime, GETDATE returns the current date and time as an encoded 64-bit signed integer:
UPDATE Orders SET LastUpdate = GETDATE()
WHERE Orders.OrderNumber=:ord
GETDATE can be used in CREATE TABLE to specify the default value for a given field. 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
-
SQL concepts: Data Type, Date and Time Constructs
-
SQL timestamp functions: CAST, CONVERT, CURRENT_TIMESTAMP, GETUTCDATE, NOW, SYSDATE, TIMESTAMPADD, TIMESTAMPDIFF, TO_POSIXTIME, TO_TIMESTAMP
-
SQL current date and time functions: CURDATE, CURRENT_DATE, CURRENT_TIME, CURTIME
-
ObjectScript: $ZDATETIME function, $HOROLOG special variable, $ZTIMESTAMP special variable