Character to Datetime error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Character to Datetime error

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2005-11-08 : 13:16:55

Hi,
I am trying to load data from TableA to TableB.But hitting an error during Character to Date Conversion.

TableB
(
id numeric primary key,--->Autogenrates
col2 NULL,
scandate datetime not null)

TableA
(
col1,
scandate char(10)
)

When I do the insert below. I hit an error. I have tried different combination of Convert value like 101,103,112

insert into TableB
select
case when ScanDate is NULL
then convert(datetime,'01/01/1980')
else
ScanDate END as ScanDate,
FROM
TABLE A

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Will appreciate any advice to fix this error.

Thanks

Raj

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-08 : 13:30:07

What does this give you?

SELECT * FROM TableA WHERE ISDATE(ScanDate)=0

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rkumar28
Starting Member

49 Posts

Posted - 2005-11-08 : 13:37:08

quote:
Originally posted by X002548

What does this give you?

SELECT * FROM TableA WHERE ISDATE(ScanDate)=0

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Hi,
From above statement, I get nulls for scandate.
I want to insert into tableB -- 01/01/1980 for every nulls for scandate in TableA.
Basically, I am trying to convert the NULLS to 01/01/1980 and insert into tableB.

Thanks

Raj

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-08 : 13:39:32

ooops....

How about this

SELECT * FROM TableA WHERE ISDATE(ScanDate)=0 AND ScanDate IS NOT NULL

My guess is that there are some rows with non valid date data, asid from Nulls

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rkumar28
Starting Member

49 Posts

Posted - 2005-11-08 : 13:45:25

quote:
Originally posted by X002548

ooops....

How about this

SELECT * FROM TableA WHERE ISDATE(ScanDate)=0 AND ScanDate IS NOT NULL

My guess is that there are some rows with non valid date data, asid from Nulls

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Thanks for pointing this out. You are correct....I found few records with these:
I think I should change my CASE statement to include these...
0
000000
0

Raj

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-08 : 13:46:00

OK, so then do this


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(ScanDate char(10))
GO

INSERT INTO myTable99(ScanDate)
SELECT null UNION ALL SELECT '01/01/2001' UNION ALL SELECT 'xxx'
GO

DECLARE @dt datetime
SELECT @dt = '1/1/1980'

SELECT CASE WHEN ISDATE(ScanDate)=1 THEN COALESCE(ScanDate,@dt) ELSE @dt END AS ScanDate
 FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rkumar28
Starting Member

49 Posts

Posted - 2005-11-08 : 14:17:41

I tried the above sql. It seems to work. But I am getting more records than TableA has in it.

Table A has 34069 records but when I do the the insert it is returning few more records(about 34073). Please advice on this....

INSERT INTO myTable99(ScanDate)
SELECT null UNION ALL SELECT '1/1/1980' UNION ALL SELECT 'xxx'
GO

DECLARE @dt datetime
SELECT @dt = '1/1/1980'

SELECT CASE WHEN ISDATE(ScanDate)=1 THEN COALESCE(ScanDate,@dt) ELSE @dt END AS ScanDate
FROM myTable99
GO

Raj

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-08 : 14:23:12

Well, with what I gave you, that's not possible.

Did you TRUNCATE the table before you ran the INSERT. Do you see more than three rows returned in the sample code I gave you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rkumar28
Starting Member

49 Posts

Posted - 2005-11-09 : 12:44:37

quote:
Originally posted by X002548

Well, with what I gave you, that's not possible.

Did you TRUNCATE the table before you ran the INSERT. Do you see more than three rows returned in the sample code I gave you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Hi Bret,
It was a problem on my end. You suggestions worked for me. Thanks Man...

Raj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources