Conversion failed when converting datetime from character string

Question

Post reply

Conversion failed when converting datetime from character string

ryan mcalister

SSC Eights!

Points: 819
More actions
July 11, 2006 at 7:55 am

#67504

I have 2 tables (baddate and gooddate) and added a ‘orderdate’ column to each.
In the baddate I added a value of 00-XXX-00 to the orderdate column.
There is no data in the gooddate table.
I’m using the following statement to try to insert the 00-XXX-00 value into the gooddate table but it will always fail with the “Conversion failed when converting datetime from character string" error.
INSERT INTO gooddate
([orderdate])
select
CONVERT(VARCHAR(50), CAST(orderdate AS DATETIME), 101)[orderdate]
from baddate
If I change the value in the baddate table to something like 03-DEC-06 the INSERT statement works perfectly and brings the value in as 12/03/2006.
Anyone have anyway to get the 00-XXX-00 values into a table as NULLs?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Try this:

INSERT INTO gooddate

([orderdate])

select

CONVERT(VARCHAR(50), CAST(nullif(orderdate, '00-XXX-00') AS DATETIME), 101)[orderdate]

from baddate

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ryan mcalister SSC Eights! Points: 819 More actions · Answer 2

Dude YOU ROCK!!!

Thank you. I have been working on this for 3 days. =)

Now I just have to figure out how to get this SQL statement into my SSIS package.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

No probs & good luck.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

manashbh_brt SSC Enthusiast Points: 138 More actions · Answer 4

Hi

I have been trying to resolve the same indof problem.

Myy counterdatetime field is of char(24) the data stays as '2010-01-24 22:30:43:234'

All I am having

CONVERT(DATETIME, CONVERT(VARCHAR(24),CounterDateTime))>=CAST('1/19/2010 10:44:18 PM' AS DATETIME)

and fails with Conversion failed when converting datetime from character string.

I tried with

CAST (RTRIM(LTRIM(CounterDateTime)) AS DATETIME )<=CAST('1/20/2010 10:48:44 PM' AS DATETIME)

but no luck..

Is there anyway, the conversion is making me fool.

Help is appriciated

Nabha SSCrazy Eights Points: 8534 More actions · Answer 5

ooaaaaaaa!!! see how old this thread was.. .you should've started a new topic..anyhow.. this works.

Declare @counterdatetime char(24)

SET @counterdatetime = '2010-01-24 22:30:43:234'

IF(CONVERT(DATETIME, CONVERT(VARCHAR(24),@CounterDateTime)) >= CAST('1/19/2010 10:44:18

PM' AS DATETIME))

BEGIN

PRINT 'TRUE'

END

ELSE

BEGIN

PRINT 'FALSE'

END

Is the date operand on the right hand side a constant always? or is it a column?

---------------------------------------------------------------------------------

manashbh_brt SSC Enthusiast Points: 138 More actions · Answer 6

No it is not static. The date was passed as @startdate but the CounterDateTime is read from table

(CONVERT(DATETIME, CONVERT(VARCHAR(24),@CounterDateTime)) >= CAST(@startDate AS DATETIME))

Today all I did varchar(16) instead varchar(24) and then its not throwing error..it worked