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

Image1.jpg
Output

Image2.jpg

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

Image3.jpg

Output

Image4.jpg

Ebook Download
View all
Learn
View all