Luna Tech

Tutorials For Dummies.

T-SQL: Data Types (DateTime)


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.

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

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

5. datetimeoffset

Date + Time + Timezone


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

7. time

Time Only, no Timezone

8. Summary

Use datetimeoffset, datetime2, date, time.