Convert nvarchar ISO-8601 date to datetime in SQL Server

Question

hopefully a quick one this.

The below doesn't work:

DECLARE @stringDate nvarchar(50)
SET @stringDate = '0001-01-01T12:00:00'

SELECT 
    @stringDate AS StringDate,
    CONVERT(datetime, @stringDate, 126) AS ConvertedDate

Produces the error: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

What's the simplest way of achieving the conversion?

Thanks

Solution 1

Type Datetime does not support the year 0001. Supported range is 1753 to 9999.
As a workaround you can use type DateTime2 instead

SELECT 
    @stringDate AS StringDate,
    CONVERT(datetime2, @stringDate, 126) AS ConvertedDate

Solution 2

It doesn't works because for DATETIME data type the

Date range [is] January 1, 1753, through December 31, 9999.

You have to use the new data type DATETIME2:

SET ANSI_WARNINGS OFF;  --Not recommended
SET ARITHABORT OFF;     --Not recommended
DECLARE @stringDate nvarchar(50)
SET @stringDate = '0001-01-01T12:00:00'

SELECT 
    @stringDate AS StringDate,
    CONVERT(datetime, @stringDate, 126) AS Converted_DATETIME,
    CONVERT(datetime2, @stringDate, 126) AS Converted_DATETIME2_A,
    CONVERT(datetime2(0), @stringDate, 126) AS Converted_DATETIME2_B

Results:

StringDate          Converted_DATETIME Converted_DATETIME2_A  Converted_DATETIME2_B
------------------- ------------------ ---------------------- ----------------------
0001-01-01T12:00:00 NULL               0001-01-01 12:00:00.00 0001-01-01 12:00:00
Arithmetic overflow occurred.

Note: you may change the default precision for DATETIME2 data type if your date/time values doesn't have "fractional seconds precision": DATETIME2(0).

Comments

[marc_s]

The DATE or DATETIME2 data types in SQL Server 2008 would support the 01/01/0001 date.....