Conversion failed when converting datetime from character string

[jovillanuev]

Hi Guys,

I encounter error when i converting the string into datetime.
My datetime field is shipdate and returndate.
also there is a Null value both of this field.

btw, here is my code. can you please modify my code
any idea that you can share with me to fixed this problem.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

Insert into chrismartin.dbo.TestTable3
select 
	a.esn,
	a.sku,
	a.model,
	a.failcode,
	a.Shipreturntype,
	a.Returndate,
    --Case when a.Shipdate in 'NULL' then ''
    --Else 
    case when a.Shipdate is not NULL then 
       Convert(datetime,(STR(DATEPART(yy, a.reshipdate)) + '/'+ STR(DATEPART(mm, a.reshipdate))+'/' + STR(DATEPART(dd, a.reshipdate)))) --as usshipdate
	--a.Shipdate,
    --Else
    --   a.shipdate   --= 'NULL'
    End As usshipdate,
    --else 
    a.Returndate,
    --Convert(datetime,(STR(DATEPART(yy, a.Reshipdate)) + '/'+ STR(DATEPART(mm, a.Reshipdate))+'/' + STR(DATEPART(dd, a.Reshipdate)))) as usReshipdate,
	--a.Reshipdate,
    --End as 
	a.SalesId,
	a.Asu_notes
From chrismartin.dbo.TestTable4 as a

You help is very much appreciated.

Thank you in Advance.

Jonel

[urtrivedi]

If you are already having date type (reshipdate) then why are u converting it. starting away insert it.

any way try to use
1) change yy to yyyy and change / to -

or

2) str_to_date function

[adam_k]

Please post table schema for both your tables. urtrivedi is right you don't need to convert.
Why are you using STR? Is this MS SQL?

[jovillanuev]

Hi Guys,

Thank you for the reply.
I fixed already the problem.
I can share this codes.

here you go.

Case When ISDATE(Shipdate) = 1
Then CONVERT(DATETIME,shipdate,120)
Else Cast(0 as datetime) End as shipdate,
--isdate(shipdate) as col_shipdate,
Case When ISDATE(Reshipdate) = 1
Then CONVERT(DATETIME,reshipdate,120)
Else Cast(0 as datetime) End as Reshipdate,

Regards,

Jonel