SYSDATETIME() Function in SQL Server

SYSDATETIME() :

This function in SQL Server is used to return the computer’s date and time in which the SQL Server is operating at present.

Features :

This function is used to find the computer’s date and time in which the SQL Server is operating.
This function comes under Date Functions.
This function doesn’t accept any parameter.
This function returns the output in ‘YYYY-MM-DD hh:mm:ss.mmm’ format.

Syntax :

SYSDATETIME()

Parameter :

This method doesn’t accept any parameter.

Returns :

It returns the computer’s date and time in which the SQL Server is operating in a ‘YYYY-MM-DD hh:mm:ss.mmm’ format.

Example-1 :

Using SYSDATETIME() function and getting the output.

SELECT SYSDATETIME();

Output :

2021-01-03 17:49:28.0575187

Here, the output will vary each time the code is compiled as this method returns the current date and time.

Example-2 :

Using SYSDATETIME() as a default value in the below example and getting the output.

CREATE TABLE system_date_time
(
   id_num        INT IDENTITY,
   message        VARCHAR(150) NOT NULL,
   generated_at DATETIME NOT NULL
   DEFAULT SYSDATETIME(),
   PRIMARY KEY(id_num)
);
INSERT INTO system_date_time(message)
VALUES('Its the first message.');

INSERT INTO system_date_time(message)
VALUES('system_date_time');

SELECT
     id_num,
     message,
     generated_at
FROM
     system_date_time;

Output :

  |id_num |  message              |   generated_at 
-------------------------------------------------------------  
1 | 1     | Its the first message.| 03.01.2021 18:53:56
-------------------------------------------------------------
2 | 2     | system_date_time      | 03.01.2021 18:53:56

Here, firstly you need to create a table then insert values into it then generate the required output using the SYSDATETIME() function as a default value.

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

Example-3 :

Using CONVERT() function in order to translate the output of the SYSDATETIME() function into the current date only.

SELECT CONVERT(DATE, SYSDATETIME());

Output :

2021-01-07

Here, the output may vary every time you run the code as it returns the current date.

Example-4 :

Using CONVERT() function in order to translate the output of the SYSDATETIME() function into the current time only.

SELECT CONVERT(TIME, SYSDATETIME());

Output :

06:20:12.2400986

Here, the output may vary every time you run the code as it returns the current time.

Application :

This function is used to return the current date and time of the computer in which the SQL server is operating.

Article Tags :

SQL

DBMS-SQL

SQL-Server