Conversion failed when converting date and/or time from character string

Question

I am trying to insert a datetime value (02/04/0000 00:23:14.713) into a table column with datatype "DateTime2"  . I am using SQL Server 2012.

SQL Code:

insert into [TrcLauraFeed]([ConfirmDate]) values (cast('02/04/0000 00:23:14.713' as datetime2))

Error message: Conversion failed when converting date and/or time from character string.

Table Details :

TableName - [TrcLauraFeed]

ColumnName - [ConfirmDate]

ColumnDatatype - DateTime2

Please tell how to do this. Thanks

---

Thanks, Balaji NS

Answer 1

I am trying to insert a datetime value (02/04/0000 00:23:14.713) into a table column with datatype "DateTime2" 

Helloe,

The smallest year you can have in SQL Server is 0001; year 0000 is not supported.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Look like problem with year,

select '02/04/0000 00:23:14.713',CAST( '02/04/0001 00:23:14.713' as datetime2)

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

See datetime2 (Transact-SQL) =>

Date range

0001-01-01 through 9999-12-31

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

Thanks for the response.

Let me put my requirement more specific.

But i am doing a data migration from Sybase to SQLServer2012 using SSMA.

A column in Source db( Sybase) has datatype datetime and the same in Target DB(SQLServer) has datatype datetime2.

All rows are getting migrated except a row which has column value '02/04/0000 00:23:14.713' , value since SQLServer doesnt allow year 0000.

I am not able to insert this row manully because of the same problem as the column in target db(SQLServer) is defined as Datetime2

insert into [TrcLauraFeed]([ConfirmDate]) values (cast('02/04/0000 00:23:14.713' as datetime2))

Error message: Conversion failed when converting date and/or time from character string.

Please tell how to do this. Thanks

---

Thanks, Balaji NS

Answer 5

The only option I see is to update the value in Sybase to year 0001

---

Olaf Helper

[ Blog] [ Xing] [ MVP]