Displaying current date and current time using T-SQL SELECT statement is straight forward, and SQL INSERT INTO command allows to insert new values into a table (or a variable), however this can get a little bit tricky with date, time and datetime data type fields as it requires a specific format.
In this tutorial I will give a few examples how to insert hard-coded values into date, time and datetime fields, and I will use similar examples using T-SQL functions.
For this tutorial I will use SQL Server 2008 R2 which should work with SQL Server 2012, 2014, 2016, and certain examples may not work with previous versions if they relate to new SQL Data Types like date or time.
Storing date and time in SQL Server
Let's start with some explanation. SQL Server stores date as integer and time as fraction of 1, this is because date and time have only meaning to humans. SQL Server displays date and time as in the following format YYYY-MM-DD HH:MM:SS for example 2012-01-05 15:05:23. This is more like a string (numbers and special characters), but date or/and time has special meaning which can cause some confusion, and therefore we storage using appropriate data type, so we can use it more effectively in the future when we query the data and want valid values (quality).
Please see below what is displayed when you use GETDATE() function which will give current date and time. I have also converted it to integer and float (decimal), using CAST Function, so you can see how SQL Server understands it. Understanding how SQL Server sees date and time, is very benefitial for T-SQL Developers, so I recommend to analyse this example until it "clicks". A good example is to think how would you convert integer or decimal to date or time.
Explanation note: In the example below to get integer I cast datetime to float and than use FLOOR function which rounds it down. I haven't cast it as INT as this would show the nearest integer which in this case is +1 (40903.577 would change to 40904 instead of 40903). Remember SQL Server doesn't understand dates (unlike humans that add meaning to it) and this is a very good example of that.
Now that we covered basics, let's move to our examples. See below table and fields that I will use during this tutorial. If you use SQL Server before 2008 R2 you may not have the same data types.
SQL Insert Date, Datetime and Time
Below is example that uses T-SQL GETDATE() function to insert data into a table with date, time and datetime fields.
And below is example where I type the values manually. The rules are very simple. Use single quotes to surround dates and put them in correct format YYYY-MM-DD HH:MM:SS in my case I also used miliseconds but this is optional and rarely used.
Tip: It would be so much fun if we didn't have some intersting exceptions like Using (dash) between date elements and getting an error for some languages.