AppDividend
Latest Code Tutorials

SQL SYSDATETIME Function Example

0

SQL SYSDATETIME function is a SQL Date Function, which is used to return the Current Date and Time of the system, i.e. DATETIME2, on which the SQL Server instance is running. The SYSDATETIME() function has more fractional seconds precision than the GETDATE() function.

The SYSDATETIME() is the nondeterministic function; therefore, views and columns that have expressions reference this function cannot be indexed.

SQL SYSDATETIME Function

Content Overview

SQL SYSDATETIME function is a SQL Date Function, which is used to return the Current Date and Time of a computer on which the SQL Server instance is running. 

The SYSDATETIME() function returns a current date and time as a datetime2(7) value.

This value is derived from the OS of the computer that the instance of the SQL Server is running on.

This post provides examples of the SYSDATETIME() function.

See the following syntax.

SELECT SYSDATETIME();

NOTE:

The function is similar to the GETDATE() function having some differences.

  1. SYSDATETIME returns the fractional seconds precision up to 7, whereas the GETDATE returns the fractional seconds precision up to 3.
  2. SYSDATETIME returns datetime2 as the data type, whereas the GETDATE returns DateTime as the data type. 

The function is a non-deterministic type; therefore, views and columns that have expressions reference this function cannot be indexed.

Examples:

See the following query.

SELECT SYSDATETIME () AS CURRENT_DATE;

Output:

CURRENT_DATE
2019-11-25 14:55:33.3975855

 

I am using the CONVERT() function to convert the SYSDATETIME function to the Current Date.

SELECT CONVERT (DATE, SYSDATETIME ());

Output

2019-11-25

Using CONVERT () function to convert SYSDATETIME function to Current Time.

Query

SELECT CONVERT (TIME, SYSDATETIME());

Output

15:08:33.3975855

Extract a part of the date

If you only want the part of the return value, you can use the DATEPART() to return only that part of the date/time that you’re interested in.

See the following query.

SELECT DATEPART(month, SYSDATETIME()) AS Result;

Output

+----------+
| Result   |
|----------|
| 11       |
+----------+

Sometimes there’s more than one way to get the same result in SQL Server. Here’s another example using the MONTH() function.

SELECT MONTH(SYSDATETIME()) AS Result;

Output

+----------+
| Result   |
|----------|
| 11       |
+----------+

Conclusion

If you want to get Current Date and Time of the system, i.e. DATETIME2 on which the SQL Server instance is running, then you can use SQL SYSDATETIME() Function.

Finally, SQL SYSDATETIME Function Example is over.

Related Posts

SQL Format Function

SQL Replace Function

SQL Translate Function

SQL Datatypes Example

SQL Substring Function

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.