Using DATE and TIME Data Type in SQL Server 2008

SQL Server has supported two basic date and time data types: datetime and smalldatetime. These data types have always allowed storage of date and time data combined in a single instance. But SQL Server 2008 supports four brand new date and time data types: date, time, datetime2, and datetimeoffset to separate date and time. Here I am discussing only two data type date and time among these four.

1. DATE Data Type:

• It ranges from 0001-01-01 to 9999-12-31.
• It has default format YYYY-MM-DD
• It's storage size 3 bytes compare to DateTime 8 bytes.
• It allows storing date without Time.

Example

Output

2. TIME Data Type

• Its range from 00:00:00.0000000 to 23:59:59.9999999
• Its default format hh:mm:ss[.nnnnnnn]
• Its storage size 3 to 5 bytes means TIME(0) = 3 Bytes and TIME(7) = 5 Bytes.
• Its use to store time without date.
• We can define from zero to seven places to the right of the decimal.

Example

Output