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

sql-server datetimeoffset
paraaku chiraaku picture paraaku chiraaku · Jul 11, 2012 · Viewed 11.4k times · Source

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

poongunran picture poongunran · Oct 24, 2013

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

Related questions

How can I convert a Sql Server 2008 DateTimeOffset to a DateTime

I'm hoping to convert a table which has a DATETIMEOFFSET field, down to a DATETIME field BUT recalculates the time by taking notice of the offset. This, in effect, converts the value to UTC. eg. CreatedOn: 2008-12-19 17:30:09.0000000 +11:00 that will …

Read More
Storing DateTime (UTC) vs. storing DateTimeOffset

I usually have an "interceptor" that right before reading/writing from/to the database does DateTime conversion (from UTC to local time, and from local time to UTC), so I can use DateTime.Now (derivations and comparisions) throughout the system …

Read More
Migrate SQL Server DateTime column to DateTimeOffset

I have an old table with a few rows that has a datetime column. I want to switch this to datetimeoffset but I want to be able to transfer the data that already exists. So I'm doing something like: SET …

Read More