CURRENT_TIMESTAMP (Transact-SQL)
Returns the current date and time. This function is the ANSI SQL equivalent to GETDATE.
Transact-SQL Syntax Conventions
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.
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)
