CURRENT_TIMESTAMP() Function in SQL Server

In SQL Server, the CURRENT_TIMESTAMP function is a widely used feature for retrieving the current date and time directly from the database server. It returns the exact moment when the SQL query is executed in the YYYY-MM-DDhh:mm:ss.mmm format.

In this article, We will learn about CURRENT_TIMESTAMP() Function in SQL Server by understanding various examples.

CURRENT_TIMESTAMP() Function in SQL Server

• The CURRENT_TIMESTAMP() function in SQL is used to retrieve the current date and time from the database server.
  
• It returns the timestamp of the moment when the SQL query is executed.

Key Features of CURRENT_TIMESTAMP in SQL Server

• Format: It returns the result in a YYYY-MM-DD HH:MM:SS format.
  
• Data Type: The returned value is usually of the TIMESTAMP or DATETIME data type, depending on the database system.
  
• Usage: It is often used to store the creation or modification time of records in a table.

Features of CURRENT_TIMESTAMP() Function

• This function is used to find the current date and time.
  
• This function comes under Date Functions.
  
• This function doesn't accept any parameter.
  
• This function can also be used as a default value in some codes.

Syntax:

CURRENT_TIMESTAMP

Parameter:

This method doesn't accept any parameter.

Returns:

It returns the current date as well as time and the format of the output returned is 'YYYY-MM-DD hh:mm:ss.mmm'.

Examples of CURRENT_TIMESTAMP() Function in SQL Server

Example 1:

Let's Retrieve the current date and time from the SQL Server in order to track or display the exact timestamp when a query is executed.

SELECT CURRENT_TIMESTAMP AS current_date_and_time;

Output:

Explanation:

The query `SELECT CURRENT_TIMESTAMP AS current_date_and_time;` retrieves the current date and time from the SQL Server at the moment the query is executed and displays it under the alias `current_date_and_time`.

The result is returned in the `YYYY-MM-DD hh:mm:ss.mmm` format.

Example 2:

Let's Create a table that automatically tracks the timestamp when a new record is inserted and allowing for the logging of messages along with the precise date and time of their creation without manual input of the timestamp.

CREATE TABLE current_time_stamp (
 id_num INT IDENTITY, 
 message VARCHAR(150) NOT NULL, 
 generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 PRIMARY KEY(id_num)
);

-- Insert some data
INSERT INTO current_time_stamp (message)
VALUES ('This is the first message.');

INSERT INTO current_time_stamp (message)
VALUES ('This is the second message.');

-- Read data from the table
SELECT 
 id_num, 
 message, 
 generated_at
FROM 
 current_time_stamp;

Output:

Explanation:

• The above query creates a table called `current_time_stamp` with three columns: `id_num` (an auto-incrementing primary key), `message` (a text field that cannot be null), and `generated_at` (a timestamp that automatically stores the current date and time using `CURRENT_TIMESTAMP`).
  
• When inserting records into the table, the `generated_at` field automatically captures the exact time each entry is made. The `SELECT` statement retrieves and displays the `id_num`, `message`, and `generated_at` columns from the table.

Note: For running the above code use SQL server compiler, you can also use an online compiler.

Conclusion

The CURRENT_TIMESTAMP function in SQL Server offers a simple and effective method for capturing the current date and time. Whether you are logging events or tracking changes, this function provides precise timestamping without the need for additional parameters.

Can I pass any parameters to the CURRENT_TIMESTAMP function?

No, the CURRENT_TIMESTAMP function does not accept any parameters. It simply returns the current date and time based on the server’s clock.

What is the data type returned by the CURRENT_TIMESTAMP function?

The CURRENT_TIMESTAMP function typically returns a value of the DATETIME data type, which includes both the date and the time up to milliseconds.

How does CURRENT_TIMESTAMP differ from GETDATE() in SQL Server?

Both CURRENT_TIMESTAMP and GETDATE() return the current date and time in SQL Server. However, GETDATE() is a SQL Server-specific function, whereas CURRENT_TIMESTAMP is ANSI SQL compliant and can be used across multiple database systems.