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:
- 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”:
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 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 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:
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:
SELECT CONVERT (date, GETDATE()) AS "Only Current 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:
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 sto_employees SET join_date = GETDATE() Where id = 6;
You can see the sixth record is updated by current date and time.