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.