GETDATE (Transact-SQL)GETDATE (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

將目前資料庫的系統時間戳記以 datetime 值傳回 (不含資料庫時區位移)。Returns the current database system timestamp as a datetime value without the database time zone offset. 這個值衍生自正在執行 SQL ServerSQL Server 執行個體之電腦的作業系統。This value is derived from the operating system of the computer on which the instance of SQL ServerSQL Server is running.

注意

SYSDATETIME 和 SYSUTCDATETIME 比 GETDATE 和 GETUTCDATE 具有更多小數秒數有效位數。SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET 包含系統時區位移。SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME、SYSUTCDATETIME 和 SYSDATETIMEOFFSET 可指派給任何日期和時間類型的變數。SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.

如需所有 Transact-SQLTransact-SQL 日期和時間資料類型與函數的概觀,請參閱日期和時間資料類型與函數 (Transact-SQL)For an overview of all Transact-SQLTransact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

GETDATE ( )  

傳回類型Return Type

datetimedatetime

RemarksRemarks

只要是 Transact-SQLTransact-SQL 陳述式可以參考 datetime 運算式的任何位置,它們就可以參考 GETDATE。Transact-SQLTransact-SQL statements can refer to GETDATE anywhere they can refer to a datetime expression.

GETDATE 是不具決定性的函數。GETDATE is a nondeterministic function. 在資料行中參考這個函數的檢視表和運算式無法編製索引。Views and expressions that reference this function in a column cannot be indexed.

將 SWITCHOFFSET 搭配 GETDATE() 函數使用可能會導致查詢執行速度緩慢,因為查詢最佳化工具無法取得 GETDATE 值精確的基數估計值。Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly because the query optimizer is unable to obtain accurate cardinality estimates for the GETDATE value. 建議您預先計算 GETDATE 值,然後再由查詢中指定該值,如下列範例所示。We recommend that you precompute the GETDATE value and then specify that value in the query as shown in the following example. 此外,請使用 OPTION (RECOMPILE) 查詢提示,以強制查詢最佳化工具在下次執行相同的查詢時重新編譯查詢計劃。In addition, use the OPTION (RECOMPILE) query hint to force the query optimizer to recompile a query plan the next time the same query is executed. 這可讓最佳化工具針對 GETDATE() 取得精確的基數估計值,從而產生更有效率的查詢計劃。The optimizer will then have accurate cardinality estimates for GETDATE() and will produce a more efficient query plan.

DECLARE @dt datetimeoffset = switchoffset (CONVERT(datetimeoffset, GETDATE()), '-04:00');   
SELECT * FROM t    
WHERE c1 > @dt OPTION (RECOMPILE);  
  

範例Examples

下列範例會使用六個可傳回目前日期和時間的 SQL ServerSQL Server 系統函數來傳回日期、時間或這兩者。The following examples use the six SQL ServerSQL Server system functions that return current date and time to return the date, time, or both. 由於這些值會依序傳回,因此其小數秒數可能會不同。The values are returned in series; therefore, their fractional seconds might be different.

A.A. 取得目前的系統日期和時間Getting the current system date and time

SELECT SYSDATETIME()  
    ,SYSDATETIMEOFFSET()  
    ,SYSUTCDATETIME()  
    ,CURRENT_TIMESTAMP  
    ,GETDATE()  
    ,GETUTCDATE();  

以下為結果集:Here is the result set.

SYSDATETIME()      2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME()   2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP  2007-04-30 13:10:02.047
GETDATE()          2007-04-30 13:10:02.047
GETUTCDATE()       2007-04-30 20:10:02.047

B.B. 取得目前的系統日期Getting the current system date

SELECT CONVERT (date, SYSDATETIME())  
    ,CONVERT (date, SYSDATETIMEOFFSET())  
    ,CONVERT (date, SYSUTCDATETIME())  
    ,CONVERT (date, CURRENT_TIMESTAMP)  
    ,CONVERT (date, GETDATE())  
    ,CONVERT (date, GETUTCDATE());  
  

以下為結果集:Here is the result set.

SYSDATETIME()          2007-05-03  
SYSDATETIMEOFFSET()    2007-05-03  
SYSUTCDATETIME()       2007-05-04  
CURRENT_TIMESTAMP      2007-05-03  
GETDATE()              2007-05-03  
GETUTCDATE()           2007-05-04

C.C. 取得目前的系統時間Getting the current system time

SELECT CONVERT (time, SYSDATETIME())  
    ,CONVERT (time, SYSDATETIMEOFFSET())  
    ,CONVERT (time, SYSUTCDATETIME())  
    ,CONVERT (time, CURRENT_TIMESTAMP)  
    ,CONVERT (time, GETDATE())  
    ,CONVERT (time, GETUTCDATE());  
  

以下為結果集:Here is the result set.

SYSDATETIME()      13:18:45.3490361  
SYSDATETIMEOFFSET()13:18:45.3490361  
SYSUTCDATETIME()   20:18:45.3490361  
CURRENT_TIMESTAMP  13:18:45.3470000  
GETDATE()          13:18:45.3470000  
GETUTCDATE()       20:18:45.3470000  

範例:Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

下列範例會使用三個可傳回目前日期和時間的 SQL ServerSQL Server 系統函式來傳回日期、時間或這兩者。The following examples use the three SQL ServerSQL Server system functions that return current date and time to return the date, time, or both. 由於這些值會依序傳回,因此其小數秒數可能會不同。The values are returned in series; therefore, their fractional seconds might be different.

D.D. 取得目前的系統日期和時間Getting the current system date and time

SELECT SYSDATETIME()  
    ,CURRENT_TIMESTAMP  
    ,GETDATE();  

E.E. 取得目前的系統日期Getting the current system date

SELECT CONVERT (date, SYSDATETIME())  
    ,CONVERT (date, CURRENT_TIMESTAMP)  
    ,CONVERT (date, GETDATE());  
  

F.F. 取得目前的系統時間Getting the current system time

SELECT CONVERT (time, SYSDATETIME())  
    ,CONVERT (time, CURRENT_TIMESTAMP)  
    ,CONVERT (time, GETDATE());  
  

另請參閱See Also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)