This tutorial will show you how to use the GETDATE function in SQL Server. The GETDATE function retrieves the current date and time from the SQL Server at run time. This function is very useful when working with time sensitive material, such as order dates.
In this tutorial we will create a table of called ProductOrders that will record the order ID, product name, and date of an order. When an order is displayed, the date and time the order was placed along with the order and product IDs will be shown.
Let’s create the table to specifically increment the OrderId column and call the GETDATE function at run time.
CREATE TABLE ProductOrders
OrderId int NOT NULL PRIMARY KEY IDENTITY,
ProductName nvarchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT GETDATE()
We used the IDENTITY key word to auto increment the OrderId each time an order is placed. Also note that we used the DEFAULT constraint to run the GETDATE function to retrieve the date and time from SQL Server and add the result to the column at run time. Execute the query and make sure it executes successfully.
Now that we have a table, let’s add some entries to test the function. We can add entries into the table with the INSERT INTO statement and add a value for the ProductName.
INSERT INTO ProductOrders(ProductName)
We only need to enter a value for the ProductName column because when we created the table we set the OrderId to automatically increment with every new entry into the database and the OrderDate by default is given the result of the GETDATE function, so there is no need to write values for the columns because it would defeat the purpose of using the IDENTITY and GETFUNCTION functions.
Enter a few more entries into the table such as: Apples, Bananas, and Cheese. Make sure that each entry executes successfully.
The last thing we need to do is create a query that will show all orders in the ProductOrders table. We use the SELECT statement followed by an asterisk to select all columns, and then use the FROM statement to specify the ProductOrders table.
SELECT * FROM ProductOrders
This query outputs the window below:
As you can see, as each order is added into the table, the GETDATE function records the current date and time of the entry. All orders have dates of 2011-04-05 but each were added at different times.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.