6

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

praveen
  • 12,083
  • 1
  • 41
  • 49
paraaku chiraaku
  • 61
  • 1
  • 1
  • 3
  • 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. – Steve Wellens Jul 11 '12 at 16:05
  • 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 (Build 7600: ) (Hypervisor) – paraaku chiraaku Jul 11 '12 at 16:50
  • 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 (Build 6002: Service Pack 2) (Hypervisor) – paraaku chiraaku Jul 11 '12 at 16:52
  • Can you post sample data for the column LastLogindata – praveen Jul 11 '12 at 16:57
  • Hi! Steve, How do I check default date format on the machine? – paraaku chiraaku Jul 11 '12 at 16:58
  • 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 – paraaku chiraaku Jul 11 '12 at 16:58
  • both servers have dateformat mdy – paraaku chiraaku Jul 11 '12 at 21:20
  • 2
    It appears like service pack2 is causing the issue. – paraaku chiraaku Jul 11 '12 at 21:22
  • 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. – sayap Sep 18 '12 at 21:50
  • 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. – sayap Sep 18 '12 at 22:31

4 Answers4

4

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
Saravanan Sachi
  • 2,572
  • 5
  • 33
  • 42
poongunran
  • 41
  • 3
1

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

Biswajit
  • 978
  • 3
  • 11
  • 30
Subhransu Sekhar
  • 72
  • 7
0

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
Hiram
  • 409
  • 1
  • 4
  • 13
0

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.

Belladonna
  • 161
  • 1
  • 10