In this demonstration, we will see how to work with the various Date and Time data types within in SQL Server. Prior to SQL Server 2008 we only had the DATETIME and SMALLDATETIME data types. Now we have four additional data types to help be more efficient in storing our data. We will start with an example of the DATETIME data type.

DECLARE @dtVar1 as DATETIME
SET @dtVar1 = '20120216 13:30:00'
SELECT @dtVar1 as Date_Example

In the example above, we declare a variable of the DATETIME data type and then set the value to February 16, 2012, at 1:30 pm. Notice that the date and time is surrounded with single quotes as if it is literal text. This is because SQL Server does not know how to tell the difference between dates and actual math. For example, let’s try a different format for the date to see what happens.

DECLARE @dtVar1 as DATETIME
SET @dtVar1 = 02-16-2012
SELECT @dtVar1 as Date_Example

How did we get June 15, 1894? This is because a DATETIME will begin at 1/1/1900. When SQL Server tries to translate the 02-16-2012 it thought we were adding and subtracting days. So, first, it added 2 days, then subtracted 16 days, then subtracted 2012 days.

So, to tell SQL Server that this is a date value and not actual math we use single quotes around the date and time. SQL Server will see it first as a character data type that needs to be implicitly converted to one of the date or time data types.

Now with the DATETIME, DATETIME2, SMALLDATETIME, and DATETIMEOFFSET we include both date and time as part of the value. But what happens if we don’t use one part of the value.

DECLARE @dtVar1 as DATETIME
DECLARE @dtVar2 as DATETIME
SET @dtVar1 = '02-16-2012'
SET @dtVar2 = '13:30:00'
SELECT @dtVar1 as Date_Example, 
	     @dtVar2 as Time_Example

Notice that in the dtVar1 variable where we just included the date, the time defaulted to all zeros. and in the dtVar2 variable where we only included the time, the date defaulted to January 1, 1900.

Fortunately, now in SQL Server 2008 and later we have the DATE and TIME data types that will conserve space in this situation. For example, we will just the data type for dtVar1 to be the DATE data type and the dtVar2 variable to be the TIME data type.

DECLARE @dtVar1 as DATE
DECLARE @dtVar2 as TIME
SET @dtVar1 = '02-16-2012'
SET @dtVar2 = '13:30:00'
SELECT @dtVar1 as Date_Example, 
	    @dtVar2 as Time_Example

Finally, be careful when going from one datetime data type to another as it is possible to truncate data that you might have wanted to keep in your database. To read more, here is a follow up post on Time Precision.