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