Date and Time Data Types - SQL 2008

SQL Server 2008 introduced new data types “Date” and “Time”. It holds only specific data. In prior version, we don’t have separate data type for time or date. If we need specific data, we have to use DataTime Data type which holds both. It was a drawback of the system. To overcome the problem, SQL 2008 provides 2 different data types (Time, Date).

Date Holds only Date Type.

Range of Date Data Type:

0001-01-01 through 9999-12-31

January 1, 1 A.D. through December 31, 9999 A.D.

Sample:

DECLARE @MyDate date

CREATE TABLE Table1 (Column1 date)

Even if you store Datetime value to this variable, it extract and hold only Date part. Have a look at the sample below

DECLARE @dt1 as DATE

SET @dt1 = getdate()

PRINT @dt1

Time Data Type:

Time Holds only Time Type.

Range of Time Data Type:

00:00:00.0000000 through 23:59:59.9999999

Sample:

DECLARE @time1 as time

SET @time1 = getdate()

PRINT @time1

Check the above code, this will hold only time data type.