SYSDATE and GETDATE

Returns the date in the format YYYY-MM-DD HH:MM:SS. The date represents the local current date and time, which is determined by the system on which the statement is executed.

If you are using TimesTen type mode, for information on SYSDATE, refer to documentation from previous releases of TimesTen.

SQL syntax

SYSDATE | GETDATE( )

Parameters

The SYSDATE and GETDATE functions have no parameters.

Description

• SYSDATE and GETDATE perform identically. SYSDATE is compatible with Oracle syntax, and GETDATE is compatible with Microsoft SQL Server syntax.

• SYSDATE and GETDATE have no arguments, and return a DATE value.

• The SYSDATE or GETDATE value is only retrieved during execution.

• Any required changes to the date (to incorporate a different time zone or Daylight Savings Time, for example) must occur at the system level. The date cannot be altered using SYSDATE or GETDATE.

• The SYSDATE and GETDATE functions return the DATE data type. The DATE format is 'YYYY-MM-DD HH:MM:SS'.

• SYSDATE and GETDATE are built-in functions and can be used anywhere a date expression may be used. They can be used in a INSERT...SELECT projection list, a WHERE clause or to insert values. They cannot be used with a SUM or AVG aggregate (operands must be numeric) or with a COUNT aggregate (column names are expected).

• SYSDATE and GETDATE return the same DATE value in a single SQL statement context.

• The literals TT_SYSDATE and ORA_SYSDATE are supported. TT_SYSDATE returns the TT_TIMESTAMP data type. ORA_SYSDATE returns the DATE data type.

Examples

In this example, invoking SYSDATE returns the same date and time for all rows in the table:

Command> SELECT SYSDATE FROM dual;
< 2006-09-03 10:33:43 >
1 row found. 

This example invokes SYSDATE to insert the current data and time into column datecol:

Command> CREATE TABLE t (datecol DATE);
Command> INSERT INTO t VALUES (SYSDATE);
1 row inserted.
Command> SELECT * FROM t;
< 2006-09-03 10:35:50 >
1 row found.

In this example, GETDATE inserts the same date value for each new row in the table, even if the query takes several seconds.

INSERT INTO t1 SELECT GETDATE(), col1
   FROM t2 WHERE ...;

TO_CHAR is used with SYSDATE to return the date from table dual:

Command> SELECT TO_CHAR (SYSDATE) FROM dual;
< 2006-09-03 10:56:35 >
1 row found.

This example invokes TT_SYSDATE to return the TT_TIMESTAMP data type and then invokes ORA_SYSDATE to return the DATE data type:

Command> SELECT tt_sysdate FROM dual;
< 2006-10-31 20:02:19.440611 >
1 row found.
Command> SELECT ora_sysdate FROM dual;
< 2006-10-31 20:02:30 >
1 row found.