Member Avatar for 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

  • 3 Contributors
  • 3 Replies
  • 424 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by jovillanuev

Dani AI

Generated

Conversion failures almost always mean some source values are not valid dates (empty strings, the literal 'NULL', stray text, or an ambiguous format that the server's DATEFORMAT/language doesn't accept). Good points from — if the source column is already a true datetime, no conversion is needed — and from that seeing the table schemas helps diagnose problems. The ISDATE-based fix posted by will work, but using CAST(0 AS datetime) is risky because it becomes a real date value (1900-01-01) that can be mistaken for valid data.

A more robust approach on modern SQL Server (2012+) is to let the engine attempt the conversion and return NULL on failure with TRY_CONVERT, and to normalize literal/empty strings first. Example:

-- SQL Server 2012+
SELECT TRY_CONVERT(datetime,
       NULLIF(NULLIF(LTRIM(RTRIM(shipdate)), ''), 'NULL'),
       120) AS ShipDate
FROM dbo.SourceTable;

For older servers, ISDATE(...) before CONVERT is the usual pattern — but return NULL for invalid strings (not CAST(0 AS datetime)), and be aware ISDATE's result depends on DATEFORMAT/language settings. Also: prefer storing proper datetime types at the source, cleanse data in a staging table (trim, replace 'NULL' and '' with NULL), and use an unambiguous string format (ISO: YYYYMMDD or the full ISO 8601 form) if building strings.

Operational notes: converting with functions like ISDATE/TRY_CONVERT on large columns can prevent index use — do conversions in an ETL/staging step. A tidy checklist: trim inputs, convert literal 'NULL' and empty strings to real NULL, use TRY_CONVERT (or ISDATE+CONVERT on older servers) and avoid sentinel 0 dates unless explicitly documented.

Recommended Answers

All 3 Replies

Member Avatar for 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

Member Avatar for 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?

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.