SQL Server Date and Time Data Types

SQL Server legacy data types are:
  • datetime
  • smalldatetime
However, these are not recommended for mainframe application migration. To facilitate migration, SQL Server 2008 added these data types:
Data Type Range Fractional Second Digits
date 0001-01-01 to 9999-12-31
time 00:00:00.0000000 to 23:59:59.9999999 0 to 7
datetime2 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 0 to 7
datetimeoffset

SQL Server provides considerable flexibility in reading date and time data as character strings, only a subset that is most directly relevant for migrations is presented here.

Default output format

SQL Server outputs date, time and datetime values in the following formats: yyyy-mm-dd, hh:m:ss.nnnnnnn (n is dependent on the column definition) and yyyy-mm-dd hh:mm:ss.nnnnnnn (n is dependent on the column definition).

Character string representation of date values

Format Type Date Format Example
ANSI/ISO yyyy-mm-dd 1987-10-12
Alphabetic The month is specified as a three character abbreviation or the full month name. Apr 15 1996

15 Apr 1996

1996 Apr 15

Numeric The month is specified as a number and the separator can be slash, hyphen or period. The order of day, month year parts can be changed by a TSQL SET DATEFORMAT or SET LANGUAGE statements. The initial order is determined from the default language for the login. The default format for us_english is mdy. The settings are stored in the table sys.syslanguages. 15/04/1996 04.15.1996 1996-4-15

Character string representation of time values

SQL Server accepts time values in the following formats: 14:30, 14:30:20, 14:30:20:145943, 2 PM, 2:30 PM, 2:30:20 PM 2:30:20.145943 PM.

Character string representation of datetime values

SQL Server accepts datetime values in ISO 8601 and ANSI formats, which are: yyyy-mm-ddThh:mm:ss.nnnnnn and yyyy-mm-dd hh:mm:ss.nnnnnn, respectively.

Parent topic: Date and Time Data Types