SQL Server SYSDATETIME()

SQL Server SYSDATETIME() Function

The SQL Server SYSDATETIME() function returns the current date and time of the system on which the SQL Server is running, with higher precision than GETDATE(). This function is useful for retrieving the precise current date and time.

Syntax

SELECT SYSDATETIME();

The SYSDATETIME() function does not take any arguments.

Example SQL Server SYSDATETIME() Function Queries

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

1. Basic SYSDATETIME() Example

SELECT SYSDATETIME() AS current_datetime;

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

current_datetime
------------------------------
2024-06-03 12:34:56.7891234

2. SYSDATETIME() with Other Functions

SELECT SYSDATETIME() AS current_datetime, DATEADD(day, 7, SYSDATETIME()) AS next_week;

This query returns the current date and time along with the date and time for one week in the future. The result will be:

current_datetime            next_week
------------------------------ ------------------------------
2024-06-03 12:34:56.7891234  2024-06-10 12:34:56.7891234

3. SYSDATETIME() in a Table

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

This example creates a table named orders with a column that defaults to the current date and time with high precision. The result will be:

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

4. SYSDATETIME() with a Variable

DECLARE @current_datetime DATETIME2;
SET @current_datetime = SYSDATETIME();
SELECT @current_datetime AS current_datetime;

This query uses a variable to store the current date and time with high precision and then returns it. The result will be:

current_datetime
------------------------------
2024-06-03 12:34:56.7891234

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the SYSDATETIME() 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 DATETIME2 DEFAULT SYSDATETIME()
);

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 SYSDATETIME() Function

This step involves using the SYSDATETIME() function to retrieve the current date and time for 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.7891234
2   Log entry 2  2024-06-03 12:34:56.7891234
3   Log entry 3  2024-06-03 12:34:56.7891234

Conclusion

The SQL Server SYSDATETIME() function is a powerful tool for retrieving the current date and time from the system's clock with high precision. Understanding how to use the SYSDATETIME() function and its syntax is essential for effective timestamp management and data processing in SQL Server.