Understanding SQL Server DateTime Format Default: A Comprehensive Guide

The datetime data type in SQL Server is used to store both date and time information. While still supported, Microsoft recommends using newer data types like datetime2 and datetimeoffset for new projects due to their enhanced features and alignment with SQL standards. However, understanding datetime, especially its default format and behavior, remains crucial for maintaining and working with existing SQL Server databases. This article provides an in-depth look at the datetime data type, focusing on its default format, supported string literal formats, and implications for data handling.

Delving into the SQL Server DateTime Data Type

The datetime data type in SQL Server represents a date and time of day, combining year, month, day, hour, minute, seconds, and fractional seconds. It’s based on a 24-hour clock and has been a staple in SQL Server for many versions.

Property	Value
Syntax	`DATETIME`
Usage	`DECLARE @MyDatetime DATETIME;` `CREATE TABLE Table1 (Column1 DATETIME);`
Default string literal formats	Not applicable
Date Range	January 1, 1753, to December 31, 9999
Time Range	00:00:00 to 23:59:59.997
Time Zone Offset	None
Element Ranges	`yyyy` (1753-9999), `MM` (01-12), `dd` (01-31), `HH` (00-23), `mm` (00-59), `ss` (00-59), `n*` (0-999)
Character Length	19 to 23 positions
Storage Size	8 bytes
Accuracy	Rounded to .000, .003, or .007 seconds
Default Value	`1900-01-01 00:00:00`
Calendar	Gregorian
Fractional Second Precision	No user-defined precision
Time Zone Aware	No
Daylight Saving Aware	No

It’s important to note the limitations of datetime. Its date range starts from 1753, which might be restrictive for historical data. Furthermore, it lacks time zone awareness, a critical feature for applications dealing with global data. The accuracy, rounded to milliseconds with a specific pattern, is also less precise than the newer datetime2 type.

This image indicates that the datetime data type is applicable to SQL Server.

Understanding Default String Literal Formats for DateTime

Unlike some other data types, datetime does not have a directly applicable “default string literal format” in the same way that, for example, date might default to YYYY-MM-DD. Instead, SQL Server relies on implicit conversions and specific rules when interpreting string literals as datetime values. The interpretation is heavily influenced by the session’s language and date format settings.

Numeric Date Formats

SQL Server can interpret dates provided in numeric formats, using separators like slashes (/), hyphens (-), or periods (.). The order of month, day, and year (mdy, dmy, ymd, etc.) depends on the DATEFORMAT setting of the SQL Server session.

For instance, with DATEFORMAT set to mdy (the default for us_english), 04/15/96 is interpreted as April 15, 1996. However, this same string could be interpreted differently if DATEFORMAT is set to dmy or ymd.

Date Format	Order	Example
`[0]4/15/[19]96`	`mdy`	April 15, 1996
`[0]4-15-[19]96`	`mdy`	April 15, 1996
`[0]4.15.[19]96`	`mdy`	April 15, 1996
`15/[0]4/[19]96`	`dmy`	April 15, 1996
`[19]96/[0]4/15`	`ymd`	April 15, 1996

When working with numeric date formats, ambiguity is a significant concern. 12/10/08 could be December 10, 2008, October 12, 2008, or even 1912/10/08 depending on the DATEFORMAT. To avoid misinterpretations, it’s highly recommended to use unambiguous formats, such as alphabetical or ISO 8601 formats, or explicitly specify the DATEFORMAT when inserting or querying datetime data.

This icon signifies that Azure SQL Database also supports the datetime data type.

Alphabetical Date Formats

Alphabetical formats offer more clarity as they use month names (full or abbreviated) which are less prone to misinterpretation than numeric month representations. SQL Server accepts month names in the current language setting.

Examples of alphabetical formats include:

'April 15, 1996'
'Apr 15 1996'
'15 April 1996'
'1996 April 15'

Alphabetical formats are not affected by the SET DATEFORMAT setting, making them more reliable for consistent date interpretation across different session settings. However, they are still language-dependent.

This illustrates that Azure SQL Managed Instance is also compatible with the datetime data type.

ISO 8601 Formats

The ISO 8601 format (YYYY-MM-DDTHH:mm:ss[.mmm]) is an international standard for date and time representation. It is unambiguous and language-independent, making it the most reliable string literal format for datetime in SQL Server.

Examples of ISO 8601 formats supported by datetime:

'2024-05-23T14:25:10'
'2024-05-23T14:25:10.487'
'20240523 14:25:10.487' (Unseparated ISO 8601)

The ISO 8601 format is strongly recommended for applications requiring global compatibility or when dealing with date and time data across different systems and locales. It bypasses both DATEFORMAT and SET LANGUAGE settings, ensuring consistent interpretation.

This indicates that Azure Synapse Analytics also supports the datetime data type.

ODBC Formats

ODBC (Open Database Connectivity) defines escape sequences for representing date and time values. SQL Server supports the ODBC timestamp format, which is also used by OLE DB. This format is particularly useful for applications using ADO, OLE DB, and ODBC-based APIs.

The ODBC timestamp escape sequence for datetime is: {ts 'yyyy-MM-dd HH:mm:ss[.fff]'}

Examples of ODBC formats:

{ts '1998-05-02 01:23:56.123'}
{d '1990-10-02'} (Date only)
{t '13:33:41'} (Time only)

ODBC formats provide a standardized way to pass date and time values between applications and SQL Server, independent of regional settings.

This icon confirms that Analytics Platform System (PDW) is also compatible with the datetime data type.

Rounding Behavior of DateTime Fractional Seconds

A key characteristic of the datetime data type is its rounding behavior for fractional seconds. datetime values are rounded to increments of .000, .003, or .007 seconds. This is due to its internal storage mechanism.

Consider these examples:

SELECT
    '01/01/2024 23:59:59.999' AS [User-specified value],
    CAST('01/01/2024 23:59:59.999' AS DATETIME) AS [System stored value]
UNION ALL
SELECT
    '01/01/2024 23:59:59.998',
    CAST('01/01/2024 23:59:59.998' AS DATETIME)
UNION ALL
SELECT
    '01/01/2024 23:59:59.997',
    CAST('01/01/2024 23:59:59.997' AS DATETIME)
UNION ALL
SELECT
    '01/01/2024 23:59:59.996',
    CAST('01/01/2024 23:59:59.996' AS DATETIME)
UNION ALL
SELECT
    '01/01/2024 23:59:59.995',
    CAST('01/01/2024 23:59:59.995' AS DATETIME)
UNION ALL
SELECT
    '01/01/2024 23:59:59.994',
    CAST('01/01/2024 23:59:59.994' AS DATETIME)
UNION ALL
SELECT
    '01/01/2024 23:59:59.993',
    CAST('01/01/2024 23:59:59.993' AS DATETIME)
UNION ALL
SELECT
    '01/01/2024 23:59:59.992',
    CAST('01/01/2024 23:59:59.992' AS DATETIME)
UNION ALL
SELECT
    '01/01/2024 23:59:59.991',
    CAST('01/01/2024 23:59:59.991' AS DATETIME)
UNION ALL
SELECT
    '01/01/2024 23:59:59.990',
    CAST('01/01/2024 23:59:59.990' AS DATETIME);

The results demonstrate this rounding:

User-specified value	System stored value
`01/01/2024 23:59:59.999`	`2024-01-02 00:00:00.000`
`01/01/2024 23:59:59.998`	`2024-01-01 23:59:59.997`
`01/01/2024 23:59:59.997`	`2024-01-01 23:59:59.997`
`01/01/2024 23:59:59.996`	`2024-01-01 23:59:59.997`
`01/01/2024 23:59:59.995`	`2024-01-01 23:59:59.997`
`01/01/2024 23:59:59.994`	`2024-01-01 23:59:59.993`
`01/01/2024 23:59:59.993`	`2024-01-01 23:59:59.993`
`01/01/2024 23:59:59.992`	`2024-01-01 23:59:59.993`
`01/01/2024 23:59:59.991`	`2024-01-01 23:59:59.990`
`01/01/2024 23:59:59.990`	`2024-01-01 23:59:59.990`

This rounding behavior should be considered when performing comparisons or calculations involving datetime values, especially when precision beyond milliseconds is required. For higher precision, datetime2 is the recommended data type.

This image confirms that SQL database in Microsoft Fabric also supports the datetime data type.

Converting to and from DateTime

SQL Server provides functions like CAST and CONVERT to transform data between different data types, including datetime. When converting to datetime, SQL Server will reject values it cannot recognize as valid dates or times.

Converting from Other Date and Time Types

From date: The date part is copied, and the time component is set to 00:00:00.000.
From time(n): The time part is copied, and the date component is set to 1900-01-01. If the time precision exceeds milliseconds, it’s truncated.
From smalldatetime: Hours and minutes are copied, seconds and fractional seconds are set to 0.
From datetimeoffset(n): Date and time parts are copied, time zone information is discarded. Precision is truncated if necessary.
From datetime2(n): Date and time parts are copied. Precision is truncated if necessary.

These conversions illustrate how datetime handles data from other date and time types, often involving loss of precision or time zone information.

Best Practices and Recommendations

While datetime is a legacy data type, understanding its behavior, especially its format handling, is essential for working with existing SQL Server databases. However, for new development, consider these best practices:

Prefer datetime2 or datetimeoffset for new applications: These newer types offer improved precision, wider date ranges, and time zone support.
Use ISO 8601 formats for string literals: To avoid ambiguity and ensure consistent interpretation across different regional settings, use ISO 8601 formats (e.g., YYYY-MM-DDTHH:mm:ss.mmm) when inserting or updating datetime values from string literals.
Be mindful of rounding: Understand the fractional second rounding behavior of datetime when dealing with precise time values. If necessary, switch to datetime2 for higher precision.
Explicitly handle date formats when necessary: If you must use numeric date formats, ensure you control the DATEFORMAT setting to avoid misinterpretations. However, using unambiguous formats is generally a better approach.

By understanding the nuances of the datetime data type and its default format interpretations, developers and DBAs can effectively manage date and time data in SQL Server, while making informed decisions about when to leverage the more modern alternatives.