SQL Server CURRENT_TIMESTAMP()

SQL Server CURRENT_TIMESTAMP() Function

The SQL Server CURRENT_TIMESTAMP() function returns the current date and time of the SQL Server. This function is useful for retrieving the system's current timestamp.

Syntax

SELECT CURRENT_TIMESTAMP;

The CURRENT_TIMESTAMP() function does not take any arguments.

Example SQL Server CURRENT_TIMESTAMP() Function Queries

Let's look at some examples of SQL Server CURRENT_TIMESTAMP() function queries:

1. Basic CURRENT_TIMESTAMP() Example

SELECT CURRENT_TIMESTAMP AS current_time;

This query returns the current date and time. The result will be:

current_time
-----------------------
2024-06-03 12:34:56.789

2. CURRENT_TIMESTAMP() with Other Functions

SELECT CURRENT_TIMESTAMP AS current_time, DATEADD(day, 5, CURRENT_TIMESTAMP) AS future_time;

This query returns the current date and time along with the date and time five days in the future. The result will be:

current_time            future_time
----------------------- -----------------------
2024-06-03 12:34:56.789 2024-06-08 12:34:56.789

3. CURRENT_TIMESTAMP() in a Table

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO orders (id) VALUES (1);
SELECT * FROM orders;

This example creates a table named orders with a column that defaults to the current timestamp. The result will be:

id  order_date
--- -----------------------
1   2024-06-03 12:34:56.789

4. CURRENT_TIMESTAMP() with a Variable

DECLARE @current_time DATETIME;
SET @current_time = CURRENT_TIMESTAMP;
SELECT @current_time AS current_time;

This query uses a variable to store the current timestamp and then returns it. The result will be:

current_time
-----------------------
2024-06-03 12:34:56.789

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the CURRENT_TIMESTAMP() function.

Step 1: Creating a Table

This step involves creating a new table named logs to store some sample data with timestamps.

CREATE TABLE logs (
    id INT PRIMARY KEY,
    log_message VARCHAR(255),
    log_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

In this example, we create a table named logs with columns for id, log_message, and log_time.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the logs table.

INSERT INTO logs (id, log_message) VALUES (1, 'Log entry 1');
INSERT INTO logs (id, log_message) VALUES (2, 'Log entry 2');
INSERT INTO logs (id, log_message) VALUES (3, 'Log entry 3');

Here, we insert data into the logs table.

Step 3: Using the CURRENT_TIMESTAMP() Function

This step involves using the CURRENT_TIMESTAMP() function to retrieve the current timestamp of each log entry.

SELECT id, log_message, log_time
FROM logs;

This query retrieves the id, log_message, and log_time for each row in the logs table. The result will be:

id  log_message  log_time
--- ------------- -----------------------
1   Log entry 1  2024-06-03 12:34:56.789
2   Log entry 2  2024-06-03 12:34:56.789
3   Log entry 3  2024-06-03 12:34:56.789

Conclusion

The SQL Server CURRENT_TIMESTAMP() function is a powerful tool for retrieving the current date and time of the SQL Server. Understanding how to use the CURRENT_TIMESTAMP() function and its syntax is essential for effective timestamp management and data processing in SQL Server.