1. Data Type Categories

Reference: Data Types

1. Numerics
   1. Exact numerics
   2. Approximate numerics
2. Strings
   1. Character strings
   2. Unicode character strings
   3. Binary strings
3. Date and time
4. Other data types

2. Date

Date only.

• default: 1900-01-01
• default format: YYYY-MM-DD
• range: 0001-01-01 to 9999-12-31
• when converted to datetime, hour, minute, second will be set to 0

DECLARE @date date= '2021-05-23';  
SELECT @date AS '@date'; -- 2021-05-23

DECLARE @datetime datetime= @date;  
SELECT @datetime AS '@datetime'; -- 2021-05-23 00:00:00.000

3. datetime

Recommend to NOT use. datetime isn’t ANSI or ISO 8601 compliant.

Use the time, date, datetime2 and datetimeoffset data types for new work. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Date + Time, no timezone

• default: 1900-01-01 00:00:00
• default format: NA
• range: 1753-01-01 to 9999-12-31, time 00:00:00 to 23:59:59:997

4. datetime2

Upgraded version of datetime

datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

Date + Time, no timezone

• default: 1900-01-01 00:00:00
• default format: YYYY-MM-DD hh:mm:ss[.fractional seconds]
• range: 0001-01-01 to 9999-12-31, time 00:00:00 to 23:59:59:9999999

5. datetimeoffset

Date + Time + Timezone

• default: 1900-01-01 00:00:00
• default format: YYYY-MM-DD hh:mm:ss[.fractional seconds]
• range: 0001-01-01 to 9999-12-31, time 00:00:00 to 23:59:59:9999999
• timezone range: -14:00 through +14:00

Conversion

When you convert from datetimeoffset to date and time data types, SQL Server rejects all values it cannot recognize as dates or times. use CAST and CONVERT function.

6. smalldatetime

not recommended to use.

Date + Time, no Timezone

• The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

7. time

Time Only, no Timezone

• default: 00:00:00
• default format: hh:mm:ss[.nnnnnnn] for Informatica)
• range: 00:00:00.0000000 through 23:59:59.9999999 (00:00:00.000 through 23:59:59.999 for Informatica)
• timezone range: -14:00 through +14:00

8. Summary

Use datetimeoffset, datetime2, date, time.