Export (0) Print
Expand All
Expand Minimize

CURRENT_TIMESTAMP (Transact-SQL)

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

Topic link icon Transact-SQL Syntax Conventions


CURRENT_TIMESTAMP

datetime

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)

Community Additions

ADD
Show:
© 2014 Microsoft