DATE: This data type is useful to store the dates without the time part, we can store dates starting from 00001-01-01 through 9999-12-31 i.e. January 1, 1 A.D. through December 31, 9999 A.D. It supports the Gregorian Calendar and uses 3 bytes to store the date.
DATETIME: This is a well known data type by most of us the date range supported is 01-01-1753 through 9999-12-31 or January 1, 1753, through December 31, 9999 and time range supported is 00:00:00 through 23:59:59.997. It takes 8 bytes to store the date/time data.
SMALLDATETIME: This is a data type that has the accuracy to 1 minute and useful for storing dates and time when the precision doesn't matter too much for example. The order booking date and time of a user. The range supported by this type of data type is 1990-01-01 through 2079-06-06 or January 1, 1900, through June 6, 2079 and time range between 00:00:00 through 23:59:59. The data type takes 4 fixed bytes to store the data.
DATETIME2: This is a new data type introduced in SQL Server 2008 and this date/time data type is introduced to store the high precision date and time data. The data type can be defined for variable lengths depending on the requirement. This data type also follows the Gregorian Calendar. The Time Zone can't be specified in this data type. This is still useful because it gives you a complete flexibility to store the date time data as per your requirement.
DATETIMEOFFSET: This is the new data type that is included in SQL Server 2008 and this data type is the most advanced in the league. We can store high precision date/ time with the Date Time Offset. We can't store the Time Zone like Eastern Time, Central Time etc. in the data type but can store the offset -5:00 for EST and -6:00 CST and so on.
The date range is between 0001-01-01 and 9999-12-31 or January 1,1 A.D. through December 31, 9999 A.D. and the Time Range is between 00:00:00 and 23:59:59.9999999. The offset range is between -14:00 through +14:00. The precision of the data type can be set manually and it follows the Gregorian Calendar.
TIME: Allows only Time format data.
No comments:
Post a Comment