What are the different date and time types in SQL?

The SQL server comes with various data type formats to store date, time, or date/time in a database. The various data types have their unique functions.

The SQL server has the following date or date/time data types. They are listed alongside their unique characteristics below:

Data Types

1. Time: It has the format hh:mm:ss[.nnnnnnn] and exists in the range 00:00:00.0000000 through 23:59:59.9999999. It also has a 100 nanosecond accuracy, a storage size of 3 to 5 bytes, and a user-defined fractional second precision.

It doesn’t have a time zone offset.

2. Date: It has the format YYYY-MM-DD, exists in the range 0001-01-01 through 9999-12-31, and has a day accuracy. The date data type has a storage size of 3 bytes. It does not have a user-defined fractional second precision and doesn’t have a time zone offset.

3. Smalldatetime: Smalldatetime has the format YYYY-MM-DD hh:mm:ss. It exists in the range 1900-01-01 through 2079-06-06 and has a minute accuracy. It has a storage size of 4 bytes, does not have a user-defined fractional second precision, and doesn’t have a time zone offset.

4. Datetime: The format for this data type is YYYY-MM-DDhh:mm:ss[.nnn]. It has the range 1753-01-01 through 9999-12-31 and a 0.00333second accuracy. Its storage size is 8 bytes. It does not have a user-defined fractional second precision and doesn’t have a time zone offset.

5. Datetime2: This data type has the format and range of YYYY-MM-DD hh:mm:ss[.nnnnnnn] and 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999, respectively. It has an accuracy of 100 nanoseconds and a storage size of 6 to 8 bytes. It has a user-defined fractional second precision and doesn’t have a time zone offset.

6. Datetimeoffset: Datetimeoffset has the format YYYY-MM-DD hh:mm:ss[.nnnnnnn][+|-]hh:mm and a range of 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC). Its accuracy is 100 nanoseconds and has a storage size of 8 to 10 bytes. This data type has a user-defined fractional second precision and a time zone offset.