The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value

Question

Using SQL Server 2008.I have a table called User which has a column LastLogindata with datetimeoffset datatype

The following query works on production server but not on replication server.

select top 10 CAST(LastLoginDate AS DATETIME)  from User.

I am getting the following error.The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.

Thanks

Potboiler

There may be a different default date format on the other machine. It may be trying to convert a number like 20 to a month. – Situs

production server version : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor) – Potboiler

Replication server version : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor) – Potboiler

Can you post sample data for the column LastLogindata – Ellynellynn

Hi! Steve, How do I check default date format on the machine? – Potboiler

Sample Data : 2012-07-11 09:46:49.4457873 -05:00, 2012-07-11 11:58:13.8353048 -05:00, 2011-08-11 18:32:10.1738934 -05:00, 2012-07-11 11:50:53.5568726 -05:00 – Potboiler

both servers have dateformat mdy – Potboiler

It appears like service pack2 is causing the issue. – Potboiler

Just encounter a similar error trying to convert a date plus some strings to a datetime. Query works fine in SSMS and with pymssql, but gives error with ODBC and JDBC. This only happens after upgrading to SQL Server 2008 R2 SP2 (10.50.4000.0), and is still not fixed in CU2 (10.50.4263.0). SQL Server is such a catastrophic failure. – Disinfest

In my case, changing from CAST to CONVERT get things to work with ODBC again. The JDBC one was just red herring, and wasn't affected. – Disinfest

Answer 1

Check the LastLoginDate columns value like this '0001-01-01' or '0001/01/01'.

If u have means get this error ..

Try this one

select top 10  CAST(CASE when cast(LastLoginDate  as varchar) = '0001-01-01 00:00:00' 
                         THEN NULL ELSE GETDATE() end AS DATETIME) from User

Buckels

Answer 2

If a field in database is of type datetimeoffset type, then it should contain date within range 0001-01-01 through 9999-12-31. I think the issue is the date inside your database.

Please check the official link of SQL server Click Here

Lavine

Answer 3

I solved it this way. I had an nvarchar(max) column casted as an xml and used the T-SQL expression ISDATE() to exclude the bad rows in the where clause.

where cast(DataObject as xml).value('(/DataObjects/@LastLoginDate)[1]', 'varchar(10)') is not null
and isdate(cast(DataObject as xml).value('(/DataObjects/@LastLoginDate)[1]', 'varchar(10)')) = 1

Fluorinate

Answer 4

On SQL Server 2016, I used:

CONVERT(DATETIME2, DateValueColumn) 

This worked for values that were giving errors when trying to convert to DATETIME, giving the message "The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value." The offending values had dates of 0001-01-01, as a previous answer has mentioned.

Not sure if this works on SQL Server 2008 though.

Legal