CODEWITHSUNDEEP
sql-serverdatetimeoffset
paraaku chiraaku
paraaku chiraaku

Reputation: 61

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

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

Upvotes: 6

Views: 17671

Answers (4)

Belladonna
Belladonna

Reputation: 308

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.

Upvotes: 0

Hiram
Hiram

Reputation: 467

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

Upvotes: 0

Subhransu Sekhar
Subhransu Sekhar

Reputation: 72

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

Upvotes: 1

poongunran
poongunran

Reputation: 41

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

Upvotes: 4

Related Questions