Hit enter after type your search item
Home / SQL Tutorials / What is GETDATE function in MS SQL Server?

What is GETDATE function in MS SQL Server?

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


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:


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:


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:


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:


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:


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:


SQL UPDATE GETDATE

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

This div height required for enabling the sticky sidebar