SYSDATE and GETDATE
Returns the date in the format YYYY-MM-DD HH:MI:SS
. The date represents the local current date and time, which is determined by the system on which the statement is executed.
SQL syntax
SYSDATE | GETDATE( )
Parameters
The SYSDATE
and GETDATE
functions have no parameters.
Description
-
SYSDATE
andGETDATE
perform identically.SYSDATE
is compatible with Oracle Database syntax.GETDATE
is compatible with Microsoft SQL Server syntax. -
SYSDATE
andGETDATE
have no arguments, and return aDATE
value. -
The
SYSDATE
orGETDATE
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
orGETDATE
. -
The
SYSDATE
andGETDATE
functions return theDATE
data type. TheDATE
format is'
YYYY-MM-DD HH:MI:SS
'
. -
SYSDATE
andGETDATE
are built-in functions and can be used anywhere a date expression may be used. They can be used in aINSERT...SELECT
projection list, aWHERE
clause or to insert values. They cannot be used with aSUM
orAVG
aggregate (operands must be numeric) or with aCOUNT
aggregate (column names are expected). -
SYSDATE
andGETDATE
return the sameDATE
value in a single SQL statement context. -
The literals
TT_SYSDATE
andORA_SYSDATE
are supported.TT_SYSDATE
returns theTT_TIMESTAMP
data type.ORA_SYSDATE
returns theDATE
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.