SQL Server Date and Time Data Types

Restriction: This topic applies to Windows environments only.

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

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.