What is GETDATE function in MS SQL Server?

The SQL GETDATE function returns the current date and time. For example:

SELECT GETDATE() AS "Date & Time";

Here are a few points:

  • The returned value is the datetime type by GETDATE function.
  • The current date and time are taken from the operating system where the database server is installed.
  • The date and time value is without the database timezone offset.
  • This GETDATE SQL function works in MS SQL Server 2005, 2008, 2008 R2, 2012 and 2014.
  • The returned date and time format is as follows:

YYYY-MM-DD hh:mm:ss.mmm

  • You may use this function with SELECT statement for getting the current date and time. You may also use it with the INSERT or UPDATE statements and use GETDATE() as the value for a datetime column in a table.

The example of GETDATE in SELECT statement

In this SQL query, we will get the current date and time from the system and display it with the column name “Current Date & Time”:

Query:

SELECT GETDATE() AS "Current Date & Time";

SQL GETDATE

Getting current date and time with table data set

Although this query may look useless, the purpose is just showing the usage of GETDATE with the table data retrieval.

The following query fetches the records of our example table, sto_employees and as the last column, ahead of join_date, displays the current date and time with each record:

SELECT *, GETDATE() AS "Current Date & Time"

FROM sto_employees;

The result-set:

SQL GETDATE table

The example of using GETDATE() with INSERT statement

In a situation where your table needs current date and time as entering records, you may use the GETDATE() function for the columns with datetime type.

See the example below where an INSERT statement is used for entering the records in sto_orders. For the demo, the sto_orders store’s order information entered by an employee as the order is taking place. So, using the DATETIME() for order date and time makes sense:

The INSERT/DATETIME query:

INSERT INTO sto_orders

VALUES (5,5,3,GETDATE());

The demo data before and after using this query:

SQL GETDATE INSERT

You can see the last record is entered with current date and time.

The example of getting only date

This example only gets the date part from by using the GETDATE function with CONVERT function. See how GETDATE() is used as an argument in the CONVERT function:

The Query:

SELECT CONVERT (date, GETDATE())

AS "Only Current Date";

The result:

SQL only date

Getting only time example

Similarly, you may get only time by using the CONVERT and GETDATE() function together. Instead of the date, pass time parameter in the CONVERT function as follows:

The query:

SELECT CONVERT (time, GETDATE())

AS "Only Current Time";

This should return the current time of the system.

Using with UPDATE statement

The SQL GETDATE function can also be used with the UPDATE statement for the datetime columns. In this example, the join_date column for id=6 is updated from NULL to GETDATE() value:

UPDATE/GETDATE query:

UPDATE sto_employees

SET join_date = GETDATE()

Where id = 6;

SQL UPDATE GETDATE

You can see the sixth record is updated by current date and time.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!