How to use GETDATE Function in SQL Server

by

In this SQL Server tutorial, you will learn about the GETDATE function in SQL Server that helps find the current date and time of the system.

You will learn about the syntax of GETDATE() with an example and then how to use the GETDATE() function with a table column, where you will create a table and insert the data into that table to record the time of the data insertion.

GETDATE Function in SQL Server

GETDATE() returns the current timestamp (date and time value) of the system on which the SQL Server instance runs. In other words, the GETDATE() function retrieves the data and time value of the operating system where your SQL Server is installed.

The syntax is given below.

GETDATE()

Where,

  • GETDATE(): It is a function that doesn’t require any arguments, and the return type of this function is datetime datatype, which contains data and time down to fractions of seconds.

The GETDATE() retrieves a different value for each call of this function, so it is called a non-deterministic function because its output is different with each call of this function.

Because GETDATE() is a non-deterministic function, it prevents indexing views or expressions containing this function.

For example, let’s know the current datetime of where SQL Server is currently running. For that, use the below query.

SELECT GETDATE() AS DateAndTime;
GETDATE Function in SQL Server

From the above output, the current timestamp value returned by the GETDATE() function is 2023-11-21 12:22:03.593.

It returns the datetime value in the format ‘YYYY-MM-DD hh:mm:ss.nnn’, the standard SQL format for datetime data types.

Where,

  • YYYY: It represents the four-digit year, for example, 2023.
  • MM: It represents the two-digit month, for example, 11.
  • DD: It represents the two-digit day, for example, 21.
  • hh: It represents the two-digit hour in 24-hour format, for example, 12 hours.
  • mm: It represents the two-digit minutes, for example, 22 minutes.
  • ss: It represents the two-digit seconds, for example, 03 seconds.
  • nnn: It represents the fractional seconds (milliseconds), which can be three decimal places long, for example, 593 fractional seconds.

The above is the format in which GETDATE() returns the system time (timestamp) in SQL Server.

Now move, and let’s see how to use the GETDATE function in SQL Server with table.

GETDATE Function in SQL Server with Table Column

You can use the GETDATE() function with the table column as the default value.

For example, you can set the GETDATE() as the default value for the column, so whenever the data is inserted into the table, the time of insertion should be recorded in the database.

To understand this, let’s create a table Employee with columns EmployeeID, Name, and JoinDate using the below query.

CREATE TABLE Employee (
	EmployeeID INT,
	Name VARCHAR(255),
        JoinDate datetime DEFAULT GETDATE()
	);

When you create the above Employee table, it creates a column EmployeeID, Name, and the JoinDate column (the time of joining, and defaults to the GETDATE()).

So you can see that the JoinDate column is set to GETDATE(), which is the default in this case, so whenever a record is inserted into an Employee table, this column records the timestamp of that insertion.

Insert the two records into the Employee table using the below query.

INSERT INTO Employee(EmployeeID, Name) VALUES(001,'Adam');
INSERT INTO Employee(EmployeeID, Name) VALUES(002,'Lisa');

again insert two more records using the below query.

INSERT INTO Employee(EmployeeID, Name) VALUES(003,'Rock');
INSERT INTO Employee(EmployeeID, Name) VALUES(004,'Teresa');

View the Employee table using the below query.

Select * From Employee;
GETDATE() Function in SQL Server with Table Column

When you insert the employee records into an Employee table, the joining date for each employee is recorded automatically because the JoinDate column is defined with a default value as GETDATE().

For each employee, the joining time is different, for example, Lisa, with ID equal to 3, has a joining date of 2023-11-21 at 12:51:01.163, and Rock has a joining date of 2023-11-21 at 12:52:56.150, both have same joining date but with different time that you can see in the above picture.

This is the one way to use the GETDATE function in SQL Server with table columns. You can employ it according to your requirements, for example, generating reports.

Conclusion

In this SQL Server tutorial, you learned how to get the current datetime value of the system using the GETDATE function in SQL Server. Also, while creating the table, you defined the column default value as GETDATE() and inserted the data into that table to record the joining time of each employee.

You may like to read: