CURRENT_TIMESTAMP (Transact-SQL)
Returns the current date and time. This function is the ANSI SQL equivalent to GETDATE.
.gif "Topic link icon")
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)