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

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

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