MS SQL Server GETDATE Function

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

SELECT GETDATE() AS "Date & Time";

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";

Getting current date and time with table data set

Although this query may look useless, the purpose is just to show 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:

The example of using GETDATE() with INSERT statement

In a situation where your table needs the 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 to enter the records in sto_orders.

For the demo, the sto_orders store’s order information is 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:

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

The example of getting only date

This example only gets the date part by using the GETDATE function with the 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:

Getting only time example

Similarly, you may get only time by using the CONVERT and GETDATE() functions 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;

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