CURRENT_TIMESTAMP (Transact-SQL)

Returns the current date and time. This function is the ANSI SQL equivalent to GETDATE.

Transact-SQL Syntax Conventions

Syntax

CURRENT_TIMESTAMP

Return Types

datetime

Remarks

CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed.

Date functions can be used in the SELECT statement select list or in the WHERE clause of a query.

In designing a report, CURRENT_TIMESTAMP can be used to print the current date and time every time that the report is produced. CURRENT_TIMESTAMP is also useful for tracking activity, such as logging the time a transaction occurred on an account.

Examples

A. Using CURRENT_TIMESTAMP to return the current date and time

The following example returns the value of CURRENT_TIMESTAMP and a text description.

SELECT 'The current time is: '+ CONVERT(char(30), CURRENT_TIMESTAMP)

Here is the result set.

--------------------------------------------------- 
The current time is: Feb 24 1998  3:45PM            

(1 row(s) affected)

B. Using CURRENT_TIMESTAMP as a DEFAULT constraint

The following creates a table that uses CURRENT_TIMESTAMP as a DEFAULT constraint for the sales_date column of a sales row.

USE AdventureWorks;
GO
CREATE TABLE sales2
(
 sales_id int IDENTITY(10000, 1) NOT NULL,
 cust_id  int NOT NULL,
 sales_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 sales_amt money NOT NULL,
 delivery_date datetime NOT NULL DEFAULT DATEADD(dd, 10, GETDATE())
)
GO
INSERT sales2 (cust_id, sales_amt)
   VALUES (20000, 550)

The following query selects all information from the sales2 table.

USE AdventureWorks;
GO
SELECT cust_id, sales_date 
FROM sales2;
GO

Here is the result set.

cust_id     sales_date
----------- -----------------------
20000       2005-03-09 15:13:45.017

(1 row(s) affected)

See Also

Reference

GETUTCDATE (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
System Functions (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance