Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
communitycreator

What are the different date and time types in SQL?

Salawudeen Aishat

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

RELATED TAGS

sql
communitycreator
RELATED COURSES

View all Courses

Keep Exploring